INSERT Statement

Adds rows to a specified table.

Category: Data Manipulation
Supports: EXECUTE Statement
Data source: SAS data set, SPD Engine data set, SPD Server table, SASHDAT file, Aster, DB2 under UNIX and PC, Greenplum, HAWQ, HDMD, Hive, Impala, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

Syntax

Form 1:

INSERT INTO table
{VALUES (value | NULL [, ...value | NULL])};

Form 2:

INSERT INTO table
{ (column [, ...column]) VALUES (value | NULL [, ...value | NULL]) };

Form 3:

INSERT INTO table
{ (column [, ...column]) [query-expression] };

Arguments

table

specifies the name of a table into which you are inserting rows.

Restriction If row-level permissions are in effect for the table, you cannot insert rows into the table. Row-level security is available only with SAS Federation Server. For more information about row-level security, see the SAS Federation Server documentation.

value

specifies a data value to insert into the table.

Restriction There must be one value for each column in the table or for each column in the column list (column).
Requirement If columns are specified in the column list (column), the data values list should be in the same order as the column list.
Tip You can use parameter arrays in the INSERT statement.

column

specifies the column into which you are inserting data.

Tip You can specify more than one column. The columns do not have to be in the same order as they appear in the table as long as the order of the column list and the data values list match one another.

<query-expression>

specifies any valid query expression that returns rows and where the number of columns in each row is the same as the number of items to be inserted.

See SELECT Statement

Details

Form 1: INSERT Statement without Column Names

This form of the INSERT statement that uses the VALUES clause without specifying column names can be used to insert lists of values into a table. You specify a value for each column in the table. One row is inserted for each VALUES clause. The order of the values in the VALUES clause should match the order of the columns in the table.
The following code fragment shows how the INSERT statement could be used with the VALUES clause to insert data values into the Customers table that was created in CREATE TABLE Statement. The Customers table has columns CustId, Name, Address, City, State, Country, PhoneNumber, and InitOrder.
insert into customers values (1,'Peter Frank', '300 Rock Lane', 'Boulder', 'CO', 
'United States', '3039564321', date '2012-01-14');
insert into customers values (2,'Jim Stewart', '1500 Lapis Lane', 'Little Rock', 
'AR', 'United States', '8705553978', date '2012-03-20');
insert into customers values (3,'Janet Chien', '75 Jujitsu', 'Nagasaki', '', 'Japan', 
'01181956879932', date '2012-06-07');
insert into customers values (4,'Qing Ziao', '10111 Karaje', 'Tokyo', '', 'Japan', 
'0118136774351', date '2012-10-12');
insert into customers values (5,'Humberto Sertu', '876 Avenida Blanca', 'Buenos Aires', 
'', 'Argentina','01154118435029', date '2012-12-15');
These statements add data values to the Sales table that was created in the CREATE TABLE Statement. The Sales table has columns ProdID, CustID, Totals, and Country.
insert into sales values (3234, 1, 189400, 'United States');
insert into sales values (1424, 3, 555789, 'Japan');
insert into sales values (3421, 4, 781183, 'Japan');
insert into sales values (3421, 2, 2789654, 'United States');
insert into sales values (3975, 5, 899453, 'Argentina');
Note that character values are enclosed in quotation marks.

Form 2: INSERT Statement with Column Names

This form of the INSERT statement that uses the VALUES clause with specific column names can also be used to insert lists of values into a table. You give values for the columns specified in the list of column names. The column names do not have to be in the same order as they appear in the table, but the order of the values in the VALUES clause must match the order of the column names in the INSERT column list. One row is inserted for each VALUES clause.
In the following code fragment, column names are used to add values to the Products table that was created in the CREATE TABLE Statement..
insert into products (prodid, product) values (1424, 'Rice');
insert into products (prodid, product) values (3421, 'Corn');
insert into products (prodid, product) values (3234, 'Wheat');
insert into products (prodid, product) values (3485, 'Oat');

The column list enables you to add a row that contains values for specified columns. If you do not specify the column list, values for every column in the table must be provided by the VALUES clause.

Form 3: INSERT Statement with a Query Expression

This form of the INSERT statement uses a query expression that inserts rows that were selected from another table or tables. The order of the values returned by the query expression should match the order of the columns that are specified in the list of column names. All the rows that are returned by the query expression are inserted into the table. The following restrictions should be noted when you use this form of the INSERT statement.
  • If you do not specify the column list, values for every column in the table must be provided by the query expression.
  • If you do not specify all columns in the column list, a null value is inserted for each column that is not listed.
  • If the data type of the column that is being added does not match the data type of the column in the table, an automatic type conversion is attempted.
Here is an example of an INSERT statement that contains a query expression.
insert into salesum (
    sum_sales,
    max_sale,
    min_sale,
    avg_sale)
values (
    (select sum(totals) from sales),
    (select max(totals) from sales),
    (select min(totals) from sales),
    (select avg(totals) from sales)
); 
This example uses subqueries to populate a new table, SalesSum, with aggregate values from the Sales table. A subquery returns only one row. To see the content of the table, see Sales. For more information, see Overview of Subqueries.

Inserting Date, Time, and Timestamp Values

For information about inserting date, time, and timestamp values, see Dates and Times in FedSQL.

Comparisons

The DELETE statement enables you to delete rows from a table. The UPDATE statement enables you to change rows in a table. The INSERT statement enables you to insert new rows into a table.
Last updated: February 23, 2017