DML INSERT


INSERT statement is used to insert the data into the table or view. INSERT can insert one row or more than one row at a time. Inserting into VIEW can insert the data into TABLE as well.

INSERT can insert actual data and as well as NULLs also into nullable columns. To perform INSERT operation, user should have INSERT privilege on the table.

Rules -

  • The length of the value should have the same length of column.
  • All columns of table should be present in INSERT query.
  • The type of the column value in INSERT should match with the column definition.

Insertion of data into the table can be done in two ways.

Simple Insert -


One insert can insert only one row in the table. This approach will be used in application programs.

Syntax -


INSERT INTO table-name[(columns-list)]
VALUES(corresponding data for the columns)

Mass Insert -


Mass insert is used to insert more than one row by using same or another table which has the same structure. MASS INSERT will use SELECT statement as a sub query.

Single INSERT can be used to insert set of rows. The columns returning by the SELECT statement should have the same structure of the table with INSERT.

Syntax -


INSERT INTO table-name1
	SELECT columns-list	
	FROM table-name2
	WHERE condition

How the INSERT statement used in the Program -


Inserting of Data can be done in the program by using Host variables. The steps to use INSERT in application program is:

  • Include the host variables copybook or declare the host variables in working storage section.
  • Fill the data to the HOST-variables.
  • Non nullable columns data should be provided, i.e. if no data available to fill the Host variables, initialization of Host variables required at least.
  • If need to insert the NULL value, then NULL indicator field needs be initialized with -1.
  • Execute the insert query to INSERT the data.

Syntax -


EXEC SQL
	INSERT INTO 
	Table-name (	Column-1, 
		Column-2,
			… ,
		Column-n)
	VALUES (	:Host-variable-1, 
		:Host-variable-2,
			…,
		:Host-variable-n)
END-EXEC.
Note!   If the INSERT statement executed successfully, SQLERRD(3) is set to the number of rows got inserted.

Error handling -


If INSERT query successfully inserts the data into table, then INSERT query will return +000 (SUCCESSFUL) as SQLCODE.

If any NULL value trying to insert through INSERT query and NULL indicator with value ‘-1’ not coded along with host-variable, then INSERT query abends with -305 (NULL value exception) as SQLCODE.

If inserting row is already present in the table, then INSERT query abends with -803 (Duplicate record found) as SQLCODE.

If the user has no access to table and trying to perform INSERT query on the table , then INSERT query abends with -922 (Authorization failure) as SQLCODE.