Guru: The Three Ways to Insert

May 8, 2017 Ted Holt

The INSERT statement is THE (as in the only) SQL way to add new data to a relational database table. At the risk of sounding like a GEICO commercial, “Everybody knows that.” Well, did you know that the INSERT statement supports three distinct ways to add new rows to a table?

To illustrate the three forms of INSERT, imagine that you and I work for a small company that stores goods in, and ships goods from, a warehouse. Since the company has only one warehouse, there has never been a need for a warehouse ID column in any of the tables or for a warehouse master table. However, that is about to change.

Business has been good, and the owners have decided to open another warehouse. You and I have to modify the database. We need a table in which to store warehouse data.

create table warehouses
  ( ID dec(3), Location varchar(16), Code char(3),
    primary key (ID));

We also add a warehouse ID column to other tables as necessary.

Method 1: VALUES and Row-value Expressions

There is no file maintenance program for the new warehouse master table. We’re busy with many other more-important tasks and we don’t have time to write one. For now, we’ll load the first two warehouses by hand. Here’s the command:

insert into warehouses 
   values (1, 'New Yolk', 'NY'), (2, 'Lost Angeles', 'LA');

We used the keyword VALUES followed by two row-value expressions. The table looks like this:

Method 2: SELECT

Time passes and business gets better and better. The owners buy three warehouses from another company. The sellers give us all applicable data, including data for the new (to us) warehouses, in text files. We have loaded those text files into temporary database tables. For historical purposes, management has decided to use the seller’s warehouse ID’s.

It’s time to load the warehouse data. Here’s the command:

insert into warehouses (ID, Location, Code)
   select w_id, w_Loc, w_Code 
     from WhsLoad;

INSERT uses the SELECT command to read data from the load table and add it to the master table. With the addition of the new data, the warehouse master looks like this:

Method 3: VALUES with a Host Structure

Business continues to improve and we’re opening three more warehouses. Somewhere in the time that has elapsed since the company bought those warehouses, we found a few minutes to write a file maintenance program. Now the appropriate user can add the new warehouses to the database!

Because we are smart programmers, we did not start from scratch, as if we had never written a file maintenance program before. We cloned an existing RPG program.

We could have made the file maintenance program insert one row at a time using the first method, but the program we cloned does not work that way. Instead, it can insert one or more rows at a time, taking the data from a data structure.

D Warehouse       ds                  qualified dim(12)
D   ID                           3p 0                  
D   Location                    16a   varying          
D   Code                         3a                    
                                                       
D WCount          s              3p 0 inz(3)           

exec sql                                                   
   insert into Warehouses :WCount rows values (:Warehouse);

This form of INSERT also uses the word VALUES, but VALUES is followed by an array data structure, not one or more row value expressions. The WCOUNT variable tells how many array elements are loaded with data. If the user loads the first three elements, and our program sets WCOUNT to 3 and execute the INSERT command, the table looks like this:

There you have it: one SQL statement with three distinct forms. The more forms, the more power! But everybody knows that.

RELATED STORIES

IBM Knowledge Center: INSERT