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 TR1 and 7.3 TR7 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.
This article was written for IBM i 7.4 TR1 and 7.3 TR7.
excellent
ReplyDelete