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
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.