Inserting rows using a select-statement in DB2 for i SQL

Inserting rows using a select-statement in DB2 for i SQL

We can use SELECT statement within an INSERT statement to insert rows into the table specified in INSERT clause from the table specified in SELECT clause.

Create Table STUDENT in two different libraries

-- Create Table in EASYCLASS1 library
CREATE TABLE easyclass1.Student (rollno integer, 
Name char(20),                        
subjectid integer) 

-- Create Table in Qtemp library
CREATE TABLE QTEMP.Student (rollno integer, 
Name char(20),                        
subjectid integer) 

Insert some values in STUDENT table in EASYCLASS1 library:

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

Data in table STUDENT in library EASYCLASS1

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

Insert rows using Select statement

Insert data into STUDENT table from EASYCLASS1 library to the QTEMP library

INSERT INTO qtemp.student       
     (rollno, name, subjectid)  
  SELECT rollno, name, subjectid
    FROM easyclass1.student     

5 rows inserted in STUDENT in QTEMP.

Output: Select * from qtemp.student

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