I wrote before how to SQL Insert a row into a table with the columns' defaults. Included within the last Technology Refreshes for IBM i 7.4 TR 1 and 7.3 TR 7 was an enhancement to the SQL Insert statement that would add a new row to the table with the columns' defaults without having to say " DEFAULT " for each column.

Before I show the enhanced Insert statement I need a table to insert the row into:

01 CREATE OR REPLACE TABLE MYLIB.TESTTABLE ( 02 ID_COLUMN SMALLINT GENERATED ALWAYS AS IDENTITY, 03 COLUMN1A VARCHAR(10) DEFAULT 'COL 1', 04 COLUMN1B VARCHAR(10), 05 COLUMN2A DECIMAL(3,0) DEFAULT -1, 06 COLUMN2B DECIMAL(3,0), 07 COLUMN3A DATE DEFAULT '01/01/1900', 08 COLUMN3B DATE, 09 COLUMN4A TIMESTAMP DEFAULT '0001-01-01-00.00.00.000000', 10 COLUMN4B TIMESTAMP 11 )

Line 1: As I am building this table in an IBM i partition that is 7.4 I can use the CREATE OR REPLACE . If the table already exists when I run this statement it will be replaced by the table given in this statement.

Line 2: I am using an Identity column to ensure that every row add to this file has a unique identifying column.

Line 3 – 10: The other columns in the table are paired. The first of the pair has a default value, while the second does not.

In my previous post the way I gave each column its default value is to use the value DEFAULT . As this table has nine columns I need to have nine DEFAULT in my Insert statement:

INSERT INTO MYLIB.TESTTABLE VALUES(DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, DEFAULT,DEFAULT,DEFAULT)

When I run this statement a single row is inserted into the table with the various columns defaults:

ID_COLUMN --------- 1 COLUMN1A COLUMN1B -------- -------- COL 1 - COLUMN2A COLUMN2B -------- -------- -1 - COLUMN3A COLUMN3B ---------- -------- 1900-01-01 - COLUMN4A COLUMN4B -------------------------- -------- 0001-01-01 00:00:00.000000 -

The Identity column was given the next available value, 1 . With all the other columns the "A" columns have their default values. The "B" columns had no given default, therefore, they are all null.

And now the new Insert statement:

INSERT INTO MYLIB.TESTTABLE DEFAULT VALUES

It is a lot shorter than the previous statement. There is no need to list out the individual columns. For a table with nine columns typing DEFAULT nine times is not that big a deal. But for a table of 30 columns having to make sure that there are 30 DEFAULTS , and not 29 or 31, in the insert statement is a chore. This new statement works just a well for one column of for 200 columns.

When executed the Insert statement does what is expected:

ID_COLUMN --------- 1 2 COLUMN1A COLUMN1B -------- -------- COL 1 - COL 1 - COLUMN2A COLUMN2B -------- -------- -1 - -1 - COLUMN3A COLUMN3B ---------- -------- 1900-01-01 - 1900-01-01 - COLUMN4A COLUMN4B -------------------------- -------- 0001-01-01 00:00:00.000000 - 0001-01-01 00:00:00.000000 -

The new Insert statement inserted the row with the identity column, ID_COLUMN , of 2. What are inserted are the defaults for each column.

You can learn more about the INSERT with DEFAULT VALUES SQL statement from the IBM website here.