Insert information into a table

After you create a table, you can insert, or add, information (data) into it by using the SQL INSERT statement.
  1. On the Enter SQL Statements display, type INSERT and press F4 (Prompt). The Specify INSERT Statement display is shown. 
                              Specify INSERT Statement
 
Type choices, press Enter.
 
  INTO table . . . . . . .    INVENTORY_LIST______     Name, F4 for list
    Collection . . . . . .      SAMPLECOLL__           Name, F4 for list
 
  Select columns to insert
    INTO  . . . . . . . . .   Y                        Y=Yes, N=No
  Insertion method  . . . .   1                        1=Input VALUES
                                                       2=Subselect
 
Type choices, press Enter.
 
  WITH isolation level  . .   1                 1=Current level, 2=NC (NONE)
                                                3=UR (CHG), 4=CS, 5=RS (ALL)
                                                6=RR
 
 
 
F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F20=Display full names
F21=Display statement
  2. Type the table name and schema name in the input fields as shown.
  3. Change the Select columns to insert INTO prompt to Yes.
  4. Press Enter to see the display where the columns you want to insert values into can be selected. 
                               Specify INSERT Statement
 
Type sequence numbers (1-999) to make selections, press Enter.
 
Seq  Column                Type              Length  Scale
1__  ITEM_NUMBER           CHARACTER              6     
2__  ITEM_NAME             VARCHAR               20     
3__  UNIT_COST             DECIMAL                8    2
4__  QUANTITY_ON_HAND      SMALLINT               4     
___  LAST_ORDER_DATE       DATE
___  ORDER_QUANTITY        SMALLINT               4
 
                                                                        Bottom
F3=Exit   F5=Refresh      F12=Cancel   F19=Display system column names
F20=Display entire name   F21=Display statement

    In this example, insert into four of the columns. Allow the other columns have their default value inserted. The sequence numbers on this display indicate the order that the columns and values are listed in the INSERT statement.

  5. Press Enter to show the display where values for the selected columns can be typed. 
                               Specify INSERT Statement
 
Type values to insert, press Enter.
 
Column                Value
ITEM_NUMBER           '153047'_____________________________________________
ITEM_NAME             'Pencils, red'_______________________________________
UNIT_COST             10.00________________________________________________
QUANTITY_ON_HAND      25___________________________________________________
 
 
 
 
 
 
 
 
 
 
 
                                                                        Bottom
F3=Exit      F5=Refresh   F6=Insert line   F10=Copy line   F11=Display type
F12=Cancel   F14=Delete line   F15=Split line   F24=More keys
    Note: To see the data type and length for each of the columns in the insert list, press F11 (Display type). This shows a different view of the insert values display, providing information about the column definition.
  6. Type the values to be inserted for all of the columns and press Enter. A row containing these values is added to the table. The values for the columns that were not specified have a default value inserted. For LAST_ORDER_DATE it is the null value because no default was provided and the column allows the null value. For ORDER_QUANTITY it is 20, the value specified as the default value on the CREATE TABLE statement.
  7. Type the INSERT statement on the Enter SQL Statements display as: 
    INSERT INTO SAMPLECOLL.INVENTORY_LIST
							(ITEM_NUMBER,
							 ITEM_NAME,
							 UNIT_COST,
							 QUANTITY_ON_HAND)
			VALUES (’153047 ’,
						’Pencils,red ’,
						 10.00,
						 25)
  8. To add the next row to the table, press F9 (Retrieve) on the Enter SQL Statements display. This copies the previous INSERT statement to the typing area. You can either type over the values from the previous INSERT statement or press F4 (Prompt) to use the Interactive SQL displays to enter data.
  9. Continue using the INSERT statement to add the following rows to the table.
Values not shown in the chart below should not be inserted so that the default is used. In the INSERT statement column list, specify only the column names for which you want to insert a value. For example, to insert the third row, specify only ITEM_NUMBER and UNIT_COST for the column names and only the two values for these columns in the VALUES list.
ITEM_NUMBER ITEM_NAME UNIT_COST QUANTITY_ON_HAND
153047 Pencils, red 10.00 25
229740 Lined tablets 1.50 120
544931   5.00  
303476 Paper clips 2.00 100
559343 Envelopes, legal 3.00 500
291124 Envelopes, standard    
775298 Chairs, secretary 225.00 6
073956 Pens, black 20.00 25
Add the following rows to the SAMPLECOLL.SUPPLIERS table.
SUPPLIER_NUMBER ITEM_NUMBER SUPPLIER_COST
1234 153047 10.00
1234 229740 1.00
1234 303476 3.00
9988 153047 8.00
9988 559343 3.00
2424 153047 9.00
2424 303476 2.50
5546 775298 225.00
3366 303476 1.50
3366 073956 17.00

The sample schema now contains two tables with several rows of data in each.

Parent topic: Get started with SQL