1 INSERT Adds a new row, or a number of rows, to a table or view.You can also use the INSERT statement with a cursor to assign values to the segments in a column of the LIST OF BYTE VARYING data type. Before you assign values to the segments in a column of the LIST OF BYTE VARYING data type, you must first assign a value to one or more other columns in the same row. To do this, use a positioned insert. A positioned insert is an INSERT statement that specifies an insert-only table cursor. This type of INSERT statement sets up the proper row context for subsequent list cursors to assign values to list segments. You can specify the name of a static, a dynamic, or an extended dynamic cursor in a positioned insert. If you specify a static cursor name, that cursor name must also be specified in a DECLARE CURSOR statement within the same module. See the DECLARE CURSOR statement for more information on static, dynamic, and extended dynamic cursors. When you use an INSERT statement to assign values to list segments: o The current transaction must not be read-only. o You cannot specify a cursor name that refers to an update table cursor. o Your cursor must specify an intermediate table. o The value that you assign is appended to the end of the list. 2 Environment You can use the INSERT statement: o In interactive SQL o Embedded in host language programs to be precompiled o As part of a procedure in an SQL module o In dynamic SQL as a statement to be dynamically executed 2 Format INSERT INTO -+-> ----+-+---------------------------+-+-+ +-> -----+ +-> AS --+ | | +-> CURSOR ---------------------------+ | +-------------------------------------------------------------------+ +---> DEFAULT VALUES ------------------+->-+---------------------+-+-> +--------------------------------+---+ | +-> returning-clause -+ | +---> ( -+> +-> ) -+ | | | +-------- , <----+ | | | +---------------<--------------------+ | | +-> value-clause ----------------------+ | +-> select-expr -+--------------------+--->------------------------+ +-> optimize-clause -+ value-clause = ----> VALUES -> ( -++> ----------++-> ) ---> |+> +| |+> value-expr -----------+| |+> DEFAULT --------------+| +------- , <---------------+ returning-clause = -+----------------------------------------++-------------------+-> +-> RETURNING value-expr ----------------++> INTO + +-> PLACEMENT ONLY RETURNING -+-> DBKEY -+ +-> ROWID -+ value-expr = ---+--> numeric-value-expr -----+-----> +--> char-value-expr --------+ +--> date-time-value-expr ---+ +--> interval-value-expr ----+ +--> date-vms-value-expr ----+ +--> DBKEY ------------------+ +--> NULL -------------------+ +--> ROWID ------------------+ select-expr = -+-+-> select-clause ------------+-+------+ | +-> ( select-expr ) -----------+ | | | +-> TABLE table-ref ----------+ | | +------ select-merge-clause <-------+ | +------------------- <-------------------+ +-+--------------------+--+------------------+--+--------------------+-> +-> order-by-clause -+ +-> offset-clause -+ +-> limit-to-clause -+ optimize-clause = --+---------------------------------------------------------------+---> +-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+ | | +-> TOTAL TIME --------+ | | | | +-> SEQUENTIAL ACCESS -+ | | | +-> USING ------------------+ | | +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ | | | +-> SAMPLED --+ | | | | +-> AGGRESSIVE + | | | +-> AS -----------------------+ | +---------------- <----------------------------+ select-merge-clause = -+-> EXCEPT -+--------------+---+----+-------------------+--> | +-> DISTINCT --+ | +-> CORRESPONDING --+ +-> INTERSECT -+-------------+-+ +-> NATURAL --------+ | +-> DISTINCT -+ | +-> MINUS ---------------------+ +-> UNION --+-------------+----+ +-> ALL ------+ +-> DISTINCT -+ 2 Arguments 3 column-name Specifies a list of names of columns in the table or view. You can list the columns in any order, but the names must correspond to those of the table or view. If you do not include all the column names in the list, SQL assigns a null value to those not specified, unless columns were: o Defined with a default o Based on a domain that has a default o Defined with the NOT NULL clause in the CREATE TABLE statement You cannot omit from an INSERT statement the names of columns defined with the NOT NULL clause. If you do, the statement fails. Omitting the list of column names altogether is the same as listing all the columns of the table or view in the same order as they were defined. You must omit the list of column names when using the INSERT statement to assign values to the segments in a column of data type LIST OF BYTE VARYING. Column names are not valid in this context. 3 CURSOR_cursor_name Keyword required when using cursors. You must use a cursor to insert values into any row that contains a column of the LIST OF BYTE VARYING data type. 3 DEFAULT Forces the named column to assume the default value defined for that column (or NULL if none is defined). If the DEFAULT clause is used in an INSERT statement then one of the following will be applied: o If a DEFAULT attribute is present for the column then that value will be applied during INSERT. o Else if an AUTOMATIC attribute is present for the column then that value will be applied during INSERT. This can only happen if the SET FLAGS 'AUTO_OVERRIDE' is used since during normal processing these columns are read-only. o Otherwise a NULL will be applied during INSERT. 3 DEFAULT_VALUES Specifies that every column in the table is assigned the default value (or NULL, if the column has no default value). 3 INTO_parameter Inserts the value specified to a specified parameter. The INTO parameter clause is not valid in interactive SQL. 3 INTO Syntax options: INTO table-name | INTO view-name The name of the target table or view to which you want to add a row. Inserts the value specified to a specified parameter. The INTO parameter clause is not valid in interactive SQL. 3 limit-to-clause See Select_Expressions in the Oracle Rdb SQL Reference Manual for a description of the LIMIT TO expression. 3 OPTIMIZE_AS_query_name The OPTIMIZE AS clause assigns a name to the query. Use the SET FLAGS 'STRATEGY' to see this name displayed. 3 OPTIMIZE_FOR The OPTIMIZE FOR clause specifies the preferred optimizer strategy for statements that specify a select expression. The following options are available: o FAST FIRST A query optimized for FAST FIRST returns data to the user as quickly as possible, even at the expense of total throughput. If a query can be cancelled prematurely, you should specify FAST FIRST optimization. A good candidate for FAST FIRST optimization is an interactive application that displays groups of records to the user, where the user has the option of aborting the query after the first few screens. For example, singleton SELECT statements default to FAST FIRST optimization. If optimization strategy is not explicitly set, FAST FIRST is the default. o TOTAL TIME If your application runs in batch, accesses all the records in the query, and performs updates or writes a report, you should specify TOTAL TIME optimization. Most queries benefit from TOTAL TIME optimization. o SEQUENTIAL ACCESS Forces the use of sequential access. This is particularly valuable for tables that use the strict partitioning functionality. 3 OPTIMIZE_USING_outline_name The OPTIMIZE USING clause explicitly names the query outline to be used with the select expression even if the outline ID for the select expression and for the outline are different. See the CREATE OUTLINE statement for more information on creating an outline. 3 OPTIMIZE_WITH Selects one of three optimzation controls: DEFAULT (as used by previous versions of Rdb), AGGRESSIVE (assumes smaller numbers of rows will be selected), and SAMPLED (which uses literals in the query to perform preliminary estimation on indices). The following example shows how to use this clause. SQL> select * from employees where employee_id > '00200' cont> optimize with sampled selectivity; 3 order-by-clause See Select_Expressions in the Oracle Rdb SQL Reference Manual for a description of the ORDER BY expression. 3 PLACEMENT_ONLY_RETURNING Syntax options: PLACEMENT ONLY RETURNING DBKEY | PLACEMENT ONLY RETURNING ROWID Returns the dbkey of a specified record, but does not insert any actual data. The PLACEMENT ONLY RETURNING DBKEY clause lets you determine the target page number for records that are to be loaded into the database. When you use this clause, only the area and page numbers from the dbkeys are returned. Use of this clause can improve bulk data loads. If you use the PLACEMENT ONLY clause, you can return only the dbkey values. Use the PLACEMENT ONLY RETURNING DBKEY clause only in programs that load data into an existing database and only with rows placed via a hashed index in the storage map. For more information, see the Oracle Rdb Guide to Database Design and Definition. The keyword ROWID is a synonym to the DBKEY keyword. 3 RETURNING_value_expr Returns the value of the column specified in the values list. If DBKEY or ROWID is specified, this argument returns the database key (dbkey) of the row being added. (The ROWID keyword is a synonym to the DBKEY keyword.) When the DBKEY value is valid, subsequent queries can use the DBKEY value to access the row directly. The RETURNING DBKEY clause is not valid in an INSERT statement used to assign values to the segments in a column of the LIST OF BYTE VARYING data type. 3 select-clause See Select_Expressions in the Oracle Rdb SQL Reference Manual for a description of the SELECT expression. 3 select-expr Specifies a select expression that specifies a result table. The result table can contain zero or more rows. All the rows of the result table are added to the target table named in the INTO clause. This is the only situation supported in SQL that allows you to specify a second database in a single SQL statement. The number of columns in the result table must correspond to the number of columns specified in the list of column names. If you did not specify a list of column names, the number of columns in the result table must be the same as the number of columns in the target table. For each row of the result table, the value of the first column is assigned to the first column of the target table, the second value to the second column, and so on. You cannot specify a select expression in an INSERT statement used to assign values to the segments in a column of the LIST OF BYTE VARYING data type. For detailed information on select expressions, see the Select_ Expressions HELP topic. 3 VALUES_value_expr Specifies a list of values to be added to the table as a single row. The values can be specified through parameters, qualified parameters, column select expressions, value expressions, or the default values. The values listed in the VALUES argument can be selected from another table, but both tables must reside in the same database. The number of values in the list must correspond to the number of columns specified in the list of column names. If you did not specify a column list, the number of values in the list must be the same as the number of columns in the table. The first value specified in the list is assigned to the first column, the second value to the second column, and so on. Values for IDENTITY, COMPUTED BY, and AUTOMATIC COLUMNS are not able to be inserted so these column types are not considered for the default column list. See the SQL Online Help topic INSERT EXAMPLES for an example that shows an INSERT statement with a column select expression. 2 Examples 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