SQL BASIC

DB2 SQL 103 DML : INSERT


SQL provides INSERT statement to append new data in DB Table. There are multiple ways to use SQL INSERT:

  1. Inserting rows using the VALUES clause( insert 1 or More row) :
    INSERT INTO table-name (column1, column2) VALUES (value1, value2)
    
    INSERT into abc (x , y, z) values(1,2,3) ,(4,5,6); ==> will insert 2 rows
    
  2. Inserting rows using a select-statement ( insert 0 or More) :
    INSERT INTO EMPDETAIL (EMPNUMBER, PROJNUMBER, STARTDATE, ENDDATE)
    SELECT EMPNO, PROJNO, EMSTDATE, EMENDATE FROM EMPPROJACT
  3. Inserting multiple rows using the blocked INSERT statement ( insert multiple rows) :
    INSERT INTO EMPDETAIL (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT) 10 ROWS VALUES(:DSTRUCT:ISTRUCT)

    ○ DSTRUCT has a dimension of at least ten to accommodate inserting ten rows.
    ○ ISTRUCT is a host structure array that is declared in the program.
    ○ ISTRUCT has a dimension of at least ten small integer fields for the indicators.

  4. Inserting data from a remote database :
    INSERT INTO SALES
    (SELECT * FROM REMOTESYS.TESTSCHEMA.SALES WHERE SALES_DATE = CURRENT DATE - 1 DAY)

    • a server authentication entry must exist.
    • Use the Add Server Authentication Entry (ADDSVRAUTE) command on the application requestor specifying the server name, user ID, and password. The server name and user ID must be entered in upper case.

    ADDSVRAUTE USRPRF(yourprf) SERVER(DRDASERVERNAME) USRID(YOURUID) PASSWORD(yourpwd)

IBM i developer.

View Comments
There are currently no comments.