What is a INSERT Statement?

The INSERT statement is an SQL statement used to add new rows of data to a table. It is used to add one or multiple rows to a DB2 table. We can specify values for each column, ensuring they meet the table's data type and constraint requirements. The INSERT statement can be used to:

• Add individual rows of data.
• Populate multiple rows in one command (using specific syntax).
• Set default values for unspecified columns if they have a default option.

Syntax - The basic syntax for an INSERT statement is as follows:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• table_name: The name of the table where data will be inserted.
• column1, column2, ...: The specific columns into which values will be inserted. If omitted, values must be provided for all columns in the table.
• value1, value2, ...: The actual data values to be inserted into each specified column. Values must match the data types of the corresponding columns.

Examples - Insert a new employee record into the EMPLOYEE table with specific values for each column.

INSERT INTO EMPLOYEE 
	(EMP_ID, EMP_NAME, DEPARTMENT, SALARY, JOIN_DATE)
	VALUES (101, 'John Doe', 'SALES', 55000.00, '2024-01-01');

• Inserts a new row into the EMPLOYEE table.
• Provides values for EMP_ID, EMP_NAME, DEPARTMENT, SALARY, and JOIN_DATE.
• Each value matches the data type of its corresponding column.

Using INSERT Statements in a COBOL Program

In a COBOL + DB2 program, the INSERT statement can be embedded using the EXEC SQL ... END-EXEC syntax. Host variables, declared in COBOL, are used to hold the values that will be inserted into the DB2 table.

Examples - Embedding an INSERT Statement in COBOL

EXEC SQL
	INSERT INTO EMPLOYEE 
	 (EMP_ID, EMP_NAME, DEPARTMENT, SALARY, JOIN_DATE)
	VALUES 
	(:EMP-ID, :EMP-NAME, :DEPARTMENT, :SALARY, :JOIN-DATE)
END-EXEC.

• EXEC SQL and END-EXEC delimit the SQL command within COBOL code.
• The VALUES clause includes host variables (prefixed with :) that hold the actual values to insert.
• Host variables like :EMP-ID and :EMP-NAME must be declared in the COBOL program's WORKING-STORAGE section.

Steps to Code an INSERT Statement in a COBOL Program

1. Declare Host Variables in the WORKING-STORAGE Section: Include the host variables DCLGEN or declare the host variables in WORKING-STORAGE SECTION that match the data types of the columns in the DB2 table.
2. Set Values for Host Variables: Assign values to the host variables in the PROCEDURE DIVISION, which will be used in the INSERT statement. If need to insert the NULL value, then NULL indicator field needs be initialized with -1.
3. Write the Embedded SQL INSERT Statement: Use EXEC SQL ... END-EXEC to embed the INSERT statement, referencing the COBOL host variables.
4. Execute the INSERT query to insert the data.
5. Check for SQL Errors: After executing the SQL statement, check the SQLCODE in the SQLCA (SQL Communication Area) to ensure the operation was successful.

Error Handling for the INSERT Statement in COBOL Program

In DB2, SQLCODE and SQLSTATE values indicate the result of an SQL operation. The SQL Communication Area (SQLCA) holds this information, which COBOL programs can use for error handling and displaying messages.

Common SQLCODE Values for INSERT:

• 0: Successful execution.
• -803: Duplicate key error (for tables with unique constraints).
• -305: Null value error (when inserting into a column that doesn't allow NULL).
• -922: Authorization failure (user doesn't have authroization to insert a row).

Examples - Error Handling in a COBOL Program

EXEC SQL
	INSERT INTO EMPLOYEE 
	(EMP_ID, EMP_NAME, DEPARTMENT, SALARY, JOIN_DATE)
	VALUES 
	(:EMP-ID, :EMP-NAME, :DEPARTMENT, :SALARY, :JOIN-DATE)
END-EXEC.

IF SQLCODE = -803
	DISPLAY 'Error: Duplicate key detected'
ELSE 
	IF SQLCODE < 0
		DISPLAY 'SQL Error: ' SQLCODE
	END IF
END-IF.

• SQLCODE = -803: Indicates a duplicate key error, meaning a unique constraint violation.
• SQLCODE < 0: Catches other SQL errors. STOP RUN terminates the program for severe errors.

---