Inserting rows into a table from another table

Examples

Example

The following SQL statement creates a table named TELE:

CREATE TABLE TELE
  (NAME2  VARCHAR(15)  NOT NULL,
   NAME1  VARCHAR(12)  NOT NULL,
   PHONE  CHAR(4));

The following statement copies data from DSN8C10.EMP into the newly created table:

INSERT INTO TELE
  SELECT LASTNAME, FIRSTNME, PHONENO
    FROM DSN8C10.EMP
    WHERE WORKDEPT = 'D21';

The two previous statements create and fill a table, TELE, that looks similar to the following table:

NAME2             NAME1          PHONE
===============   ============   =====
PULASKI           EVA            7831
JEFFERSON         JAMES          2094
MARINO            SALVATORE      3780
SMITH             DANIEL         0961
JOHNSON           SYBIL          8953
PEREZ             MARIA          9001
MONTEVERDE        ROBERT         3780

The CREATE TABLE statement example creates a table which, at first, is empty. The table has columns for last names, first names, and phone numbers, but does not have any rows.

The INSERT statement fills the newly created table with data that is selected from the DSN8C10.EMP table: the names and phone numbers of employees in department D21.

Example

The following CREATE statement creates a table that contains an employee's department name and phone number. The fullselect within the INSERT statement fills the DLIST table with data from rows that are selected from two existing tables, DSN8C10.DEPT and DSN8C10.EMP.

CREATE TABLE DLIST
  (DEPT    CHAR(3)      NOT NULL,
   DNAME   VARCHAR(36)          ,
   LNAME   VARCHAR(15)  NOT NULL,
   FNAME   VARCHAR(12)  NOT NULL,
   INIT    CHAR                 ,
   PHONE   CHAR(4) );

INSERT INTO DLIST
  SELECT DEPTNO, DEPTNAME, LASTNAME, FIRSTNME, MIDINIT, PHONENO
    FROM DSN8C10.DEPT, DSN8C10.EMP
    WHERE DEPTNO = WORKDEPT;