Examples

Example 1: Insert a new department with the following specifications into the DEPARTMENT table:

  INSERT INTO DEPARTMENT
    VALUES ('E31', 'ARCHITECTURE', '00390', 'E01')

Example 2: Insert a new department into the DEPARTMENT table as in example 1, but do not assign a manager to the new department.

  INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
    VALUES ('E31', 'ARCHITECTURE', 'E01')

Example 3: Create a table MA_EMPPROJACT with the same columns as the EMPPROJACT table. Populate MA_EMPPROJACT with the rows from the EMPPROJACT table with a project number (PROJNO) starting with the letters 'MA'.

  CREATE TABLE MA_EMPPROJACT LIKE EMPPROJACT

  INSERT INTO MA_EMPPROJACT
    SELECT * FROM EMPPROJACT
      WHERE SUBSTR(PROJNO, 1, 2) = 'MA'

Example 4: Use a Java(TM) program statement to add a skeleton project to the PROJECT table on the connection context 'ctx'. Obtain the project number (PROJNO), project name (PROJNAME), department number (DEPTNO), and responsible employee (RESPEMP) from host variables. Use the current date as the project start date (PRSTDATE). Assign a NULL value to the remaining columns in the table.

  #sql [ctx] { INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE)
              VALUES (:PRJNO, :PRJNM, :DPTNO, :REMP, CURRENT DATE) };

Example 5: Insert two new departments using one statement into the DEPARTMENT table as in example 2, but do not assign a manager to the new departments.

  INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
    VALUES ('B11', 'PURCHASING', 'B01'),
           ('E41', 'DATABASE ADMINISTRATION', 'E01')

Example 6: In a PL/I program, use a multiple-row INSERT to add 10 rows to table DEPARTMENT. The host structure array DEPT contains the data to be inserted.

   DCL  1  DEPT(10),
           3  DEPT  CHAR(3),
           3  LASTNAME  CHAR(29)  VARYING,
           3  WORKDEPT  CHAR(6),
           3  JOB  CHAR(3);

   EXEC SQL INSERT INTO DEPARTMENT 10 ROWS VALUES (:DEPT);

Example 7: Insert a new project into the EMPPROJACT table using the Read Uncommitted (UR, CHG) option:

   INSERT INTO EMPPROJACT
     VALUES ('000140', 'PL2100', 30)
     WITH CHG