A new way to list columns for SQL insert

The latest Technology Refreshes introduced a new way to give the columns I am inserting with an SQL Insert statement into a Table or file.

This is the DDL Table I will be inserting into:

01 CREATE TABLE RPGPGM1.TEST_TABLE 02 (IDENTITY BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL, 03 LAST_NAME FOR COLUMN "LNAME" VARCHAR(30), 04 FIRST_NAME FOR COLUMN "FNAME" VARCHAR(20), 05 ADDRESS_CITY FOR COLUMN "CITY" VARCHAR(20), 06 ADDRESS_STATE FOR COLUMN "STATE" CHAR(2), 07 ADDRESS_COUNTRY FOR COLUMN "COUNTRY" CHAR(3)) ;

Line 2: This is an Identity column, which Db2 for i is responsible for calculating the value that is inserted into this column.

The column names explain what each of the other columns will be used for.

The "old" way to insert a row into a table like this is to have a column list of the columns I will be inserting, and then a VALUES for the list of values that are inserting:

01 INSERT INTO TEST_TABLE 02 (LAST_NAME,FIRST_NAME,CITY,STATE,COUNTRY) 03 VALUES('SMITH','JONAS','COLUMBUS','OH','USA')

Line 2: The list of columns to have values inserted into.

Line 3: The values have to align with the column list.

The "new" way is to give the column names, with the values to be inserted into that column, in the values clause:

01 INSERT INTO TEST_TABLE 02 VALUES(COUNTRY => 'USA', 03 CITY => 'ORLANDO', 04 STATE => 'FL', 05 FIRST_NAME => 'JANE', 06 LAST_NAME => 'MERRIMAN')

I put the column names in the values in a different order than the way they are found in the Table. The column name is followed by "=>", and then the value to insert into that column. In this example lines 2 – 4 use the (short) system names for the columns, and line 5 and 6 use the (long) SQL names.

I can also use the "new" way in a RPG program:

01 **free 02 dcl-s FirstName char(20) inz('MARY') ; 03 dcl-s LastName char(20) inz('TAYLOR') ; 04 dcl-s City char(20) inz('ALEXANDRIA') ; 05 dcl-s State char(2) inz('VA') ; 06 exec sql INSERT INTO TEST_TABLE 07 VALUES(LAST_NAME => :LastName, 08 FIRST_NAME => :FirstName, 09 CITY => :City, 10 STATE => :State, 11 COUNTRY => 'USA') ; 12 *inlr = *on ;

Lines 2 – 5: These variables contain the values that will be inserted into TEST_TABLE.

Lines 6 – 11: The SQL statement to insert the new row into the Table.

Having run all three Insert statements, the first two in ACS's Run SQL Scripts and the third from the RPG program, I can check the file to make sure that all three rows were added without any problems:

01 SELECT * FROM TEST_TABLE

The results show the three rows were successfully added, each one with its own unique Identity column.

FIRST_ ADDRESS_ ADDRESS ADDRESS_ IDENTITY LAST_NAME NAME CITY _STATE COUNTRY -------- --------- ------- --------- ------- -------- 1 SMITH JONAS COLUMBUS OH USA 2 MERRIMAN JANE ORLANDO FL USA 3 TAYLOR MARY ALEXANDRIA VA USA

I must admit I like the way the "new" values looks, compared to the old. On a large insert statement, I would think it would be a lot easier to understand what values are being inserted into each column using the "new" way.

 

You can learn more about the "new" Insert values from the IBM website here.

 

This article was written for IBM i 7.6 TR1 and 7.5 TR7.