V5R3 SQL Insert Improvement

Published: January 4, 2006

by Ted Holt

SQL's INSERT command adds records to a table (physical file) or view (logical file). There are three forms of INSERT. The VALUES form allows you to create a record from constants. For example, assume a table PLANT with two columns (fields)--ID and NAME.

create table qtemp/Plants
  (ID   char(4),         
   Name char(12))

To create rows (records) for two factories, you could use two insert commands.

insert into qtemp/plants values('1492', 'Lost Angeles')
insert into qtemp/plants values('2001', 'New Yolk')

In V5R3, IBM enhanced the VALUES form of INSERT to permit you to insert more than one row at a time. The following INSERT command inserts two rows.

insert into qtemp/plants
   values ('1492', 'Lost Angeles'), ('2001', 'New Yolk')

Each row's values are enclosed in parentheses and separated from other rows with commas.

This is not an IBM-only feature. Other SQL platforms support it as well.