5.3. Populating Tables
After a table is created, it can then be populated with data. Unless the newly created table is defined as a subset of an existing table or its content is to remain static, one or more rows of data may eventually need to be added. The SQL standard provides the INSERT INTO statement as the vehicle for adding rows of data. The examples in this section look at how to add data in all the columns in a row as well as how to add data in only some of the columns in a row.
5.3.1. Adding Data to All the Columns in a Row
You populate tables with data by using an INSERT INTO statement. In fact, the INSERT INTO statement really doesn’t insert rows of data at all. It simply adds each row to the end of the table. Three parameters are specified with an INSERT INTO statement: the name of the table, the names of the columns in which values are inserted, and the values themselves. Data values are inserted into a table with a VALUES clause. Suppose you want to insert (or add) a single row of data to the CUSTOMERS table and the row consists of three columns (Customer Number, Customer Name, and Home City).
SQL Code
PROC SQL;
INSERT INTO CUSTOMERS (CUSTNUM, CUSTNAME, CUSTCITY)
VALUES (702, 'Mission Valley Computing', 'San Diego'),
QUIT;The SAS log displays the following message noting that one row was inserted into the CUSTOMERS table.
SAS Log Results
PROC SQL;
INSERT INTO CUSTOMERS
(CUSTNUM, CUSTNAME, CUSTCITY)
VALUES (702, 'Mission Valley Computing',
|
'San Diego'),
NOTE: 1 row was inserted into CUSTOMERS.
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.54 secondsThe inserted row of data from the previous INSERT INTO statement is added to the end of the CUSTOMERS table.
Entering a new row into a table containing an index will automatically add the value to the index (for more information on indexes, see Chapter 6, “Modifying and Updating Tables and Indexes”). The following example illustrates adding three rows of data using the VALUES clause.
SQL Code
PROC SQL;
INSERT INTO CUSTOMERS
(CUSTNUM, CUSTNAME, CUSTCITY)
VALUES (402, 'La Jolla Tech Center', 'La Jolla')
VALUES (502, 'Alpine Byte Center', 'Alpine')
VALUES (1702,'Rancho San Diego Tech','Rancho San Diego'),
SELECT *
FROM CUSTOMERS
ORDER BY CUSTNUM;
QUIT;The SAS log shows that the three rows of data were inserted into the CUSTOMERS table.
SAS Log Results
PROC SQL;
INSERT INTO CUSTOMERS
(CUSTNUM, CUSTNAME, CUSTCITY)
VALUES (402, 'La Jolla Tech Center', 'La
|
The new rows are displayed in ascending order by CUSTNUM.
Customer
Number Customer Name Customer's
|
5.3.2. Adding Data to Some of the Columns in a Row
It is not all that uncommon when adding rows of data to a table, to have one or more columns with an unassigned value. When this happens SQL must be able to handle adding the rows to the table as if all the values were present. But how does SQL handle values that are not specified? You will see in the following example that SQL assigns missing values to columns that do not have a value specified. As before, three parameters are specified with the INSERT INTO statement: the name of the table, the names of the columns in which values are inserted, and the values themselves. Suppose you had to add two rows of incomplete data to the CUSTOMERS table where two of three columns were specified (Customer Number and Customer Name).
SQL Code
PROC SQL;
INSERT INTO CUSTOMERS
(CUSTNUM, CUSTNAME)
VALUES (102, 'La Mesa Byte & Floppy')
VALUES (902, 'Del Mar Technology Center'),
SELECT *
FROM CUSTOMERS
ORDER BY CUSTNUM;
QUIT;The SAS log shows the two rows of data added to the CUSTOMERS table.
SAS Log Results
PROC SQL;
INSERT INTO CUSTOMERS
(CUSTNUM, CUSTNAME)
VALUES (102, 'La Mesa Byte & Floppy')
VALUES (902, 'Del Mar Technology Center'),
NOTE: 2 rows were inserted into WORK.CUSTOMERS.
SELECT *
FROM CUSTOMERS
ORDER BY CUSTNUM;
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.00 seconds |
The new rows are displayed in ascending order by CUSTNUM with missing values assigned to the character column CUSTCITY.
Customer
Number Customer Name
|
In the previous example, missing values were assigned to the character column CUSTCITY. Suppose you want to add two rows of partial data to the PRODUCTS table where four of the five columns are specified (Product Number, Product Name, Product Type, and Product Cost) and the missing value for each row is the numeric column MANUNUM.
SQL Code
PROC SQL;
INSERT INTO PRODUCTS
(PRODNUM, PRODNAME, PRODTYPE, PRODCOST)
VALUES(6002,'Security Software','Software',375.00)
VALUES(1701,'Travel Laptop SE', 'Laptop', 4200.00);
SELECT *
FROM PRODUCTS
ORDER BY PRODNUM;
QUIT;The SAS log displays the two rows of data added to the PRODUCTS table.
PROC SQL;
INSERT INTO PRODUCTS
(PRODNUM, PRODNAME, PRODTYPE,
|
The new rows are displayed in ascending order by PRODNUM with missing values assigned to the numeric column MANUNUM.
Product Manufacturer |
5.3.3. Adding Data with a SELECT Query
You can also add data to a table using a SELECT query with an INSERT INTO statement. A query expression essentially executes an enclosed query by first creating a temporary table and then inserting the contents of the temporary table into the target table being populated. In the process of populating the target table, any columns omitted from the column list are automatically assigned to missing values.
In the next example, a SELECT query is used to add four rows of data from the SOFTWARE_PRODUCTS table into the PRODUCTS table. The designated query controls the insertion of data into the target PRODUCTS table using a WHERE clause.
SQL Code
PROC SQL;
INSERT INTO PRODUCTS
(PRODNUM, PRODNAME, PRODTYPE, PRODCOST)
SELECT PRODNUM, PRODNAME, PRODTYPE, PRODCOST
FROM SOFTWARE_PRODUCTS
WHERE PRODTYPE IN ('Software'),
QUIT;The SAS log displays the results of the four rows of data added to the PRODUCTS table.
SAS Log Results
PROC SQL;
INSERT INTO PRODUCTS
(PRODNUM, PRODNAME, PRODTYPE, PRODCOST)
SELECT PRODNUM, PRODNAME, PRODTYPE, PRODCOST
FROM SOFTWARE_PRODUCTS
WHERE PRODTYPE IN ('Software'),
NOTE: 4 rows were inserted into WORK.PRODUCTS.
QUIT;
NOTE: PROCEDURE SQL used:
real time 0.04 seconds
cpu time 0.01 seconds |