Here are four SQL INSERT Queries you can use in DB2. Also, explained how to get diagnostics of inserted rows.
1). What are basic rules of INSERT in DB2?
- The INSERT statement inserts rows into a table or view or activates the INSTEAD OF INSERT trigger.
- The table or view can be at the current server or any DB2® subsystem with which the current server can establish a connection.
- Inserting a row into a view inserts the row into the table on which the view is based if no INSTEAD OF INSERT trigger is defined on the specified view.
- If an INSTEAD OF INSERT trigger is defined, the trigger is activated instead of the INSERT statement.
Four SQL INSERT Queries
1). The INSERT via VALUES form is used to insert a single row into the table or view using the values provided or referenced.
2). The INSERT via SELECT form is used to insert one or more rows into the table or view using values from other tables, or views, or both.
3). The INSERT via FOR n ROWS form is used to insert multiple rows into the table or view using values provided or referenced. Although not required, the values can come from host-variable arrays.
4) This form of INSERT is supported in SQL procedure applications. However, since host-variable arrays are not supported in SQL procedure applications, the support is limited to insertion of scalar values.
Syntax for INSERT
EXEC SQL Insert into table_name Values (:v1,:v2) END-EXEC
2). What are different issues with multiple row insert?
ATOMIC or NOT ATOMIC CONTINUE ON SQLEXCEPTION Specifies whether all of the rows should be inserted as an atomic operation or not.
ATOMIC Specifies that if the insert for any row fails, all changes made to the database by any of the inserts, including changes made by successful inserts, are undone. This is the default (ATOMIC key word you can use only in INSERT SQL query)
Example Query to insert multiple rows:
EXEC SQL INSERT INTO T1FOR :hv ROWSVALUES (:hva:hvind) ATOMIC;
NOT ATOMIC CONTINUE ON SQL EXCEPTION
Specifies that, regardless of the failure of any particular insert of a row, the INSERT statement will not undo any changes made to the database by the successful inserts of other rows, and inserting will be attempted for subsequent rows.
However, the minimum level of atomicity is at least that of a single insert (that is, it is not possible for a partial insert to complete), including any triggers that might have been executed as a result of the INSERT statement
3). How many rows can insert into a Table with multiple insert option?
Host-variable or integer-constant is assigned to an integral value k. If host-variable is specified, it must be an exact numeric type with scale zero, and must not include an indicator variable. Furthermore, k must be in the range, 0<k<=32767. k rows are inserted into the target table from the specified source data. (So, in DB2 maximum 32767 rows can be inserted at a time into a Table).
4). How to get INSERT diagnostics after running a Query?
After execution of the INSERT statement, the following information will be in the SQLCA:
SQLCODE = -253 (An error is returned)SQLSTATE = 22529SQLERRD3 = 8 (Number of rows successfully inserted)
During INSERT, you will come across other SQL error code (i.e., other than duplicate record) is how many records successfully inserted or how many records failed during insert.
Different diagnostics information SQL queries:
GET DIAGNOSTICS:num_rows = ROW_COUNT,:num_cond = NUMBER;
The result of this statement is num_rows = 8 and num_cond = 2 (2 conditions).
GET DIAGNOSTICS CONDITION 2:sqlstate = RETURNED_SQL:sqlcode = DB2_RETURNED_SQLCODE, :row_num = DB2_ROW_NUMBER;
The result of this statement is sqlstate = 22003, sqlcode = -302, and row_num = 4.
GET DIAGNOSTICS CONDITION 1:sqlstate = RETURNED_SQLSTATE,:sqlcode = DB2_RETURNED_SQLCODE,:row_num = DB2_ROW_NUMBER;
The result of this statement is sqlstate = 22003, sqlcode = -302, and row_num = 8.
So, with the above SQL query, you will know what are the rows failed during insert.
Related Posts
Srinimf 