Example 1: Adding a row with literal values
The following interactive SQL example stores a new row in the
DEPARTMENTS table of the sample personnel database. It explicitly
assigns a literal value to each column in the row. Because the
statement includes the RETURNING DBKEY clause, SQL returns the
dbkey value 29:435:9.
SQL> INSERT INTO DEPARTMENTS
cont> -- List of columns:
cont> (DEPARTMENT_CODE,
cont> DEPARTMENT_NAME,
cont> MANAGER_ID,
cont> BUDGET_PROJECTED,
cont> BUDGET_ACTUAL)
cont> VALUES
cont> -- List of values:
cont> ('RECR',
cont> 'Recreation',
cont> '00175',
cont> 240000,
cont> 128776)
cont> RETURNING DBKEY;
DBKEY
29:435:9
1 row inserted
Example 2: Adding a row using parameters
This example is a COBOL program fragment that adds a row to the
JOB_HISTORY table by explicitly assigning values from parameters
to columns in the table. This example:
o Prompts for the column values.
o Declares a read/write transaction. Because you are updating
the JOB_HISTORY table, you do not want to conflict with other
users who may be reading data from this table. Therefore, you
use the protected share mode and the write lock type.
o Stores the row by assigning the parameters to the columns of
the table.
o Checks the value of the SQLCODE variable and repeats the
INSERT operation if the value is less than zero.
o Uses the COMMIT statement to make the update permanent.
STORE-JOB-HISTORY.
DISPLAY "Enter employee ID: " WITH NO ADVANCING.
ACCEPT EMPL-ID.
DISPLAY "Enter job code: " WITH NO ADVANCING.
ACCEPT JOB-CODE.
DISPLAY "Enter starting date: " WITH NO ADVANCING.
ACCEPT START-DATE.
DISPLAY "Enter ending date: " WITH NO ADVANCING.
ACCEPT END-DATE.
DISPLAY "Enter department code: " WITH NO ADVANCING.
ACCEPT DEPT-CODE.
DISPLAY "Enter supervisor's ID: " WITH NO ADVANCING.
ACCEPT SUPER.
EXEC SQL
SET TRANSACTION READ WRITE
RESERVING JOB_HISTORY FOR PROTECTED WRITE
END-EXEC
EXEC SQL
INSERT INTO JOB_HISTORY
(EMPLOYEE_ID,
JOB_CODE,
JOB_START,
JOB_END,
DEPARTMENT_CODE,
SUPERVISOR_ID)
VALUES (:EMPL-ID,
:JOB-CODE,
:START-DATE,
:END-DATE,
:DEPT-CODE,
:SUPER)
END-EXEC
IF SQLCODE < 0 THEN
EXEC SQL ROLLBACK END-EXEC
DISPLAY "An error has occurred. Try again."
GO TO STORE-JOB-HISTORY
END-IF
EXEC SQL COMMIT END-EXEC
Example 3: Copying from one table to another
This interactive SQL example copies a subset of data from the
EMPLOYEES table to an identical intermediate result table. To do
this, it uses a select expression that limits rows of the select
expression's result table to those with data on employees who
live in New Hampshire.
SQL> INSERT INTO TEMP
cont> (EMPLOYEE_ID,
cont> LAST_NAME,
cont> FIRST_NAME,
cont> MIDDLE_INITIAL,
cont> ADDRESS_DATA_1,
cont> ADDRESS_DATA_2,
cont> CITY,
cont> STATE,
cont> POSTAL_CODE,
cont> SEX,
cont> BIRTHDAY,
Cont> STATUS_CODE)
cont> SELECT * FROM EMPLOYEES
cont> WHERE STATE = 'NH';
90 rows inserted
SQL>
Example 4: Copying rows between databases with the INSERT
statement
This example copies the contents of the EMPLOYEES table from the
personnel database to another database, LOCALDATA.
SQL> ATTACH 'ALIAS PERS FILENAME personnel';
SQL> ATTACH 'ALIAS LOCALDB FILENAME localdata';
SQL>
SQL> DECLARE TRANSACTION
cont> ON PERS USING (READ ONLY
cont> RESERVING PERS.EMPLOYEES FOR SHARED READ)
cont> AND
cont> ON LOCALDB USING (READ WRITE
cont> RESERVING LOCALDB.EMPLOYEES FOR SHARED WRITE);
SQL>
SQL> INSERT INTO LOCALDB.EMPLOYEES
cont> SELECT * FROM PERS.EMPLOYEES;
100 rows inserted
SQL>
Example 5: Adding data to columns of data type LIST OF BYTE
VARYING
The following interactive SQL example adds a new row to the
RESUMES table of the sample personnel database. It first assigns
a value to the EMPLOYEE_ID column, then adds three lines of
information to the RESUME column of the same row. The RESUME
column has the LIST OF BYTE VARYING data type. You must specify
the name of the list column (RESUME) in addition to the table
column when declaring the table cursor for a positioned insert.
SQL> DECLARE TBLCURSOR INSERT ONLY TABLE CURSOR FOR SELECT EMPLOYEE_ID, RESUME
cont> FROM RESUMES;
SQL> DECLARE LSTCURSOR INSERT ONLY LIST CURSOR FOR SELECT RESUME
cont> WHERE CURRENT OF TBLCURSOR;
SQL> OPEN TBLCURSOR;
SQL> INSERT INTO CURSOR TBLCURSOR (EMPLOYEE_ID) VALUES ('00167');
1 row inserted
SQL> OPEN LSTCURSOR;
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('This is the resume for 00167');
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Boston, MA');
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Oracle Corporation');
SQL> CLOSE LSTCURSOR;
SQL> CLOSE TBLCURSOR;
SQL> COMMIT;
Example 6: Using the PLACEMENT ONLY RETURNING DBKEY clause of the
INSERT statement
SQL> INSERT INTO EMPLOYEES
cont> (EMPLOYEE_ID, LAST_NAME, FIRST_NAME)
cont> VALUES
cont> ('5000', 'Parsons', 'Diane')
cont> PLACEMENT ONLY RETURNING DBKEY;
DBKEY
56:34:-1
1 row allocated
SQL>
Example 7: Inserting the user name and an amount into table
columns:
SQL> CREATE TABLE TABLE1
cont> (ID CHAR(15),
cont> AMOUNT INT(4));
SQL> INSERT INTO TABLE1 (ID, AMOUNT)
cont> VALUES (USER, 1000);
SQL> SELECT * FROM TABLE1;
ID AMOUNT
ELLINGSWORTH 1000.0000
1 row selected
Example 8: Inserting a name and a column select expression into
the same table columns used in the previous example:
SQL> INSERT INTO TABLE1 (ID, AMOUNT)
cont> VALUES ('BROWN',
cont> (SELECT COUNT (*) FROM TABLE1));
SQL> SELECT * FROM TABLE1;
ID AMOUNT
HALVORSON 1000.0000
BROWN 1.0000
2 rows selected
Example 9: Inserting Default Values for Selected Columns
SQL> INSERT INTO DEPARTMENTS
cont> (DEPARTMENT_CODE, DEPARTMENT_NAME, BUDGET_ACTUAL)
cont> VALUES
cont> ('RECR','Recreation', DEFAULT);
1 row inserted
SQL> SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_CODE='RECR';
DEPARTMENT_CODE DEPARTMENT_NAME MANAGER_ID
BUDGET_PROJECTED BUDGET_ACTUAL
RECR Recreation NULL
NULL NULL
1 row selected
Example 10: Inserting a Row of All Default Values into a Table
SQL> INSERT INTO CANDIDATES
cont> DEFAULT VALUES;
1 row inserted
SQL> SELECT * FROM CANDIDATES
cont> WHERE LAST_NAME IS NULL;
LAST_NAME FIRST_NAME MIDDLE_INITIAL
CANDIDATE_STATUS
RESUME
NULL NULL NULL
NULL
>>
>>
>>
NULL
1 row selected