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.
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 seconds |
The 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 Jolla') VALUES (502, 'Alpine Byte Center', 'Alpine') VALUES (1701,'Rancho San Diego Tech' ,'Rancho San Diego'), NOTE: 3 rows were inserted into WORK.CUSTOMERS. SELECT * FROM CUSTOMERS ORDER BY CUSTNUM; QUIT; NOTE: PROCEDURE SQL used: real time 1.03 seconds |
The new rows are displayed in ascending order by CUSTNUM.
Customer Number Customer Name Customer's Home City ________________________________________________ _________ 101 La Mesa Computer Land La Mesa 201 Vista Tech Center Vista 301 Coronado Internet Zone Coronado 401 La Jolla Computing La Jolla 402 La Jolla Tech Center La Jolla 501 Alpine Technical Center Alpine 502 Alpine Byte Center Alpine 601 Oceanside Computer Land Oceanside 701 San Diego Byte Store San Diego 702 Mission Valley Computing San Diego 801 Jamul Hardware & Software Jamul 901 Del Mar Tech Center Del Mar 1001 Lakeside Software Center Lakeside 1101 Bonsall Network Store Bonsall 1201 Rancho Santa Fe Tech Rancho Santa Fe 1301 Spring Valley Byte Center Spring Valley 1401 Poway Central Poway 1501 Valley Center Tech Center Valley Center 1601 Fairbanks Tech USA Fairbanks Ranch 1701 Blossom Valley Tech Blossom Valley 1702 Rancho San Diego Tech Rancho San Diego 1801 Chula Vista Networks |
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 Customer's Home City _______________________________________________ __________ 101 La Mesa Computer Land La Mesa 102 La Mesa Byte & Floppy 201 Vista Tech Center Vista 301 Coronado Internet Zone Coronado 401 La Jolla Computing La Jolla 402 La Jolla Tech Center La Jolla 501 Alpine Technical Center Alpine 502 Alpine Byte Center Alpine 601 Oceanside Computer Land Oceanside 701 San Diego Byte Store San Diego 702 Mission Valley Computing San Diego 801 Jamul Hardware & Software Jamul 901 Del Mar Tech Center Del Mar 902 Del Mar Technology Center 1001 Lakeside Software Center Lakeside 1101 Bonsall Network Store Bonsall 1201 Rancho Santa Fe Tech Rancho Santa Fe 1301 Spring Valley Byte Center Spring Valley 1401 Poway Central Poway 1501 Valley Center Tech Center Valley Center 1601 Fairbanks Tech USA Fairbanks Ranch 1701 Rancho San Diego Tech Rancho San Diego 1701 Blossom Valley Tech Blossom Valley 1801 Chula Vista Networks |
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, PRODCOST) VALUES(6002,'Security Software','Software' ,375.00) VALUES(1701,'Travel Laptop SE', 'Laptop', 4200.00); NOTE: 2 rows were inserted into WORK.PRODUCTS. SELECT * FROM PRODUCTS ORDER BY PRODNUM; QUIT; NOTE: PROCEDURE SQL used: real time 0.75 seconds |
The new rows are displayed in ascending order by PRODNUM with missing values assigned to the numeric column MANUNUM.
Product Manufacturer Product Number Product Name Number Product Type Cost _______________________________________________ ________________ 1110 Dream Machine 111 Workstation $3,200.00 1200 Business Machine 120 Workstation $3,300.00 1700 Travel Laptop 170 Laptop $3,400.00 1701 Travel Laptop SE . Laptop $4,200.00 2101 Analog Cell Phone 210 Phone $35.00 2102 Digital Cell Phone 210 Phone $175.00 2200 Office Phone 220 Phone $130.00 5001 Spreadsheet Software 500 Software $299.00 5002 Database Software 500 Software $399.00 5003 Wordprocessor Software 500 Software $299.00 5004 Graphics Software 500 Software $299.00 6002 Security Software . Software $375.00 |
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 |