SQLRPGLE Insert Statement for db2 in iSeries (AS400)

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

Sample SQLRPGLE Program to Insert data in db2 on iSeries(AS400)

d $cmp            s              2s 0                        
d $empId          s              5s 0                        
d $empName        s             30a                          
d $empDate        s              8s 0                        
d $empSalary      s             11s 2                        
                                                             
 /free                                                       
                                                             
  //Set SQL options                                          
  exec sql                                                   
   SET OPTION                                                
       commit=*none,                                         
       datfmt=*iso;                                          
                                                             
  //Insert data into SQL table                               
  exec sql                                                   
    INSERT INTO QGPL/MYDATA VALUES(                           
       1, 101, 'John', 20110101, 100.01);                    
  exec sql                                                   
    INSERT INTO QGPL/MYDATA (d1cmp,d1id,d1name,d1sdat,d1saly) 
       VALUES(1, 102, 'Todd', 20110202, 100.02);             
                                                             
  exec sql                                                    
    INSERT INTO QGPL/MYDATA (d1cmp,d1id,d1name,d1sdat)
       VALUES(1, 103, 'Alex', 20110303);             
                                                     
  //Insert data from fields into SQL table           
    $cmp       = 1;                                  
    $empId     = 104;                                
    $empName   = 'Kevin';                            
    $empDate   = 20110404;                           
    $empSalary = 100.04;                             
                                                     
  exec sql                                           
    INSERT INTO QGPL/MYDATA VALUES(                   
       :$cmp,                                        
       :$empId,                                      
       :$empName,                                    
       :$empDate,                                    
       :$empSalary);      */                         
                                                     
  //Insert data from similar SQL table               
  exec sql                                           
    INSERT INTO QGPL/MYDATA (                         
       SELECT * from qgpl/mydata_s);                  
                                                     
  //Insert data from another SQL table                           
  exec sql                                                       
    INSERT INTO QGPL/MYDATA (                                     
       SELECT d1cmp,d1id,d1name,d1sdat,d1saly from qgpl/mydata_s);

  exec sql                                                      
    INSERT INTO QGPL/MYDATA                                     
       (mydata_fld1, mydata_fld2, mydata_fld3,...) SELECT                                  
   mydata_s_fld1, mydata_s_fld2, mydata_s_fld3,...  
       from QGPL/MYDATA_S ;                                            
                                                                 
  *inlr = *on;                                                   
                                                                 
 /end-free

Host Variables

Host variables are always preceded in SQL by a colon. Since your RPGLE program is the "host" of these SQL statements, the term host variable refers to any variable declared in the RPGLE program. We can use these variables when we execute SQL as parameters or as return values.

INSERT Statement

Insert statements have the following form:

INSERT INTO table (column1, [column2, ... ]) VALUES (value1, [value2, ... ])

The number of columns and values must be the same. If a column is not specified, the default value for the column is used. Shorthand may also be used, taking advantage of the order of the columns when the table was created. It is not required to specify all columns in the table since any other columns will take their default value or remain null:

INSERT INTO table VALUES (value1, [value2, ... '])

Multirow inserts

A SQL feature (since SQL-92) is the use of row value constructors to insert multiple rows at a time in a single SQL statement:

INSERT INTO ''TABLE'' (''column-a'', [''column-b'', ...])

VALUES (''value-1a'', [''value-1b'', ...]),

(''value-2a'', [''value-2b'', ...]),

...