SQL INSERT INTO Statement in DB2 for i SQL

SQL INSERT INTO Statement in DB2 for i SQL

The INSERT INTO statement is used to insert single or multiple records in a table or view.

Syntax using INSERT INTO

Use both column name/values

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

Use only values

Order of the values must be in the same order as the columns in the table.

INSERT INTO table_name
VALUES (value1, value2, value3, ...)

Create Table STUDENT

-- Create Table
CREATE TABLE Student (rollno integer, 
Name char(20),                        
subjectid integer)  

Insert single record in the STUDENT table.

INSERT INTO Student VALUES (ROLLNO, NAME, SUBJECTID)
(1,'Ankur',201)

Or

INSERT INTO Student VALUES
(1,'Ankur',201)

Output

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201

Insert Data Only in Specified Columns in STUDENT table

INSERT INTO Student VALUES (ROLLNO, NAME)
(1,'Ankur')

Output

Default values was not specified for the column during table creation therefore, the default insert value would be NULL if column is not specified during INSERT and value is not assigned to it.

ROLLNO   NAME                      SUBJECTID 
     1   ANKUR                              -

Insert multiple rows in STUDENT table

Here, we separate each set of values with comma.

INSERT INTO Student VALUES  (ROLLNO, NAME, SUBJECTID)
(1,'Ankur',201),          
(2,'Rahul',202),          
(3,'Raman',203),          
(4,'Vimal',204),
(5,'Samar',290)

Or

INSERT INTO Student VALUES
(1,'Ankur',201),          
(2,'Rahul',202),          
(3,'Raman',203),          
(4,'Vimal',204),
(5,'Samar',290)

Output

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202
     3   Raman                           203
     4   Vimal                           204
     5   Samar                           290