DB2 - INSERT Statement

First Page
Next Page

DB2 INSERT Statement

You can use an INSERT statement to add new rows to a table or view. You can use an INSERT statement to take the following actions −

Specify the values to insert in a single row. You can specify constants, host variables, expressions, DEFAULT, or NULL. Use host variable arrays in the VALUES clause of the INSERT FOR n ROWS statement to insert multiple rows into a table. Include a SELECT statement in the INSERT statement to tell DB2® that another table or view contains the data for the new row or rows.

You can add new data to an existing table in other ways, too. You might need to add large amounts of data to an existing table. Some efficient options include copying a table into another table, writing an application program that enters data into a table, and using the DB2 LOAD utility to enter data.

Suppose that you want to add a new row to the NEWDEPT table. Use this INSERT statement - 

 INSERT INTO NEWDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
   VALUES ('E31', 'PUBLISHING', '000020', 'D11');

After inserting the new department row into the NEWDEPT table, you can use a SELECT statement to see what the modified table looks like. Use this query - 

SELECT *
  FROM NEWDEPT
  WHERE DEPTNO LIKE 'E%'
  ORDER BY DEPTNO;

The result table gives you the new department row that you inserted for department E31 and the existing departments with a department number beginning in E.

DEPTNO     DEPTNAME             MGRNO      ADMRDEPT
======     ================     ======     ========
E21        SOFTWARE SUPPORT     ------          D11
E31        PUBLISHING           000020          D11

What is DB2 INSERT Statement ?

An SQL INSERT statement adds one or more records to any single table in a relational database. In order to create an RPGLE source member that can use embedded SQL, you must use a source type of SQLRPGLE.

To compile an SQLRPGLE source member, use the "Create SQL ILE RPG Object" (CRTSQLRPGI) command. If you are using PDM, option 14 will create a bound program, and option 15 will create a *MODULE object. If you are issuing the command manually, use the OBJTYPE parameter to indicate *PGM or *MODULE. Also, if your program updates a file that is not journaled, you must change the command option COMMIT to *NONE, otherwise the updates will not occur.

Its better to use the SET OPTION SQL statement. It allows you to enforce certain program options in the code itself rather than on the compile command which you might forget to do.

c/exec sql
c+ set option commit=*none,
c+ datfmt=*iso
c/end-exec

Syntax

The syntax for the INSERT statement when inserting a single record in SQL is - 

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... );

Or the syntax for the INSERT statement when inserting multiple records in SQL is - 

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_tables
[WHERE conditions];

The following will generate an insert statement for you in DB2.

create or replace function insertme(
schemaname varchar(50),
tablename varchar(50),
rrnno integer
)
returns varchar(20000)
begin
declare mysql varchar(20000);
declare mysql2 varchar(20000);
DECLARE C1 CURSOR
FOR DYNSQL;
select
'select ''insert into '||tablename||' values(''||'
|| listagg(''''''''' concat rtrim(replace(' ||column_name || ','''''''',''''''''''''))||''''''''',' ||'','' || ')
|| '||'')'''
|| ' from '||schemaname||'.'||tablename||' a where rrn(a)=' || rrnno
into mysql
from syscolumns where table_name=tablename and table_schema=schemaname;
PREPARE DYNSQL from mysql;
OPEN C1;
fetch from c1 into mysql2;
close c1;
return mysql2;
end
;

Example use:
select insertme(schema_name,table_name,rrn(a))
from schema_name.table_name a limit 1

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.

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)
First Page
Next Page