The INSERT Statement
A Brief Overview
You can use the INSERT statement
in three ways to insert rows of data into existing, empty, or populated
tables.
|
Method of Inserting
Row
|
Example
|
|---|---|
|
insert values by column
name by using the SET clause
|
proc sql; insert into work.discount set destination='LHR', begindate='01MAR2018'd, enddate='05MAR2018'd, discount=.33 set destination='CPH', begindate='03MAR2018'd, enddate='10MAR2018'd, discount=.15; quit; |
|
insert lists of values
by using the VALUES clause
|
proc sql; insert into work.discount (destination, begindate,enddate,discount) values ('LHR','01MAR2018'd, '05MAR2018'd,.33) values ('CPH','03MAR2018'd, '10MAR2018'd,.15); quit; |
|
insert rows that are
copied from another table by using a query result
|
proc sql; insert into payrollchanges2 select empid,salary,dateofhire from certadv.payrollmaster where empid in ('1919','1350','1401'); quit; |
In each method, the
INSERT statement inserts new rows of data into the table. To indicate
that the rows have been inserted, the SAS log displays a message similar
to the following.
Log 2.5 SAS Log
NOTE: 2 row was inserted into WORK.DISCOUNT.
The SET Clause
A Brief Overview
Suppose you need to add rows
of data to a table, but the data is not currently contained in any
table. In this situation, you can use either the SET clause or the
VALUES clause in the INSERT statement to specify the data to be added.
The SET clause in the
INSERT statement enables you to specify new data to be added to a
table. The SET clause specifies column names and values in pairs.
PROC SQL reads each column name-value pair and assigns the value to
the specified column. A separate SET clause is used for each row to
be added to the table.
SET Clause Syntax
The
syntax of the INSERT statement that contains the SET clause is shown
below.
|
Syntax, INSERT statement
containing the SET clause:
INSERT INTO table-name <(target-column-1<,
... target-column-n)>
SET column-1=value-1<,
... column-n=value-n>
<...
SET column-1=value-1<,
... column-n=value-n>>;
table-name
specifies the name
of the table to which rows will be inserted.
target-column
specifies the name
of a column into which data will be inserted.
each SET clause
specifies one or more
values to be inserted in one or more specified columns in a row. Multiple
SET clauses are not separated by commas.
column
specifies the name
of a column into which data will be inserted.
value
specifies a data value
to be inserted into the specified column. Character values must be
enclosed in quotation marks.
multiple column=value pairs
in a SET clause
are separated by commas.
|
Note: It is optional to include
a list of target column names after the table name in the INSERT TABLE
statement that includes a SET clause. The list can include the names
of all or only a subset of columns in the table. If you specify an
optional list of target column names, then you can specify values
only for columns that are in the list. You can list target columns
in any order, regardless of their position in the table. Any columns
that are in the table but not listed are given missing values in the
inserted rows.
Note: Although it is recommended
that the SET clause list column-value pairs in order (as they appear
in the table or the optional column list), it is not required.
Example: Inserting Rows by Using the SET Clause
Consider the table Work.Discount,
which was presented in the last topic. Work.Discount stores airline
discounts for certain flight destinations and time periods in March.
The following example
illustrates these points:
-
adding two rows of new data to Work.Discount by using an INSERT statement that contains two SET clauses, one for each row
-
generating a report that displays Work.Discount, with its two new rows, by using a SELECT statement
In this situation, you
do not need to include an optional list of column names.
proc sql;
insert into work.discount
set destination='LHR',
begindate='01MAR2018'd,
enddate='05MAR2018'd,
discount=.33
set destination='CPH',
begindate='03MAR2018'd,
enddate='10MAR2018'd,
discount=.15;
select *
from work.discount;
quit;
Because SELECT * was
used in the query, the displayed output includes all six rows of data.
If you ran the previous examples, Work.Discount displays six rows
of data and not just the two that were inserted.
Output 2.2 PROC SQL Query Result: Inserting Two Rows
The following is printed
to the SAS log.
Log 2.6 SAS Log
NOTE: 2 rows were inserted into WORK.DISCOUNT.
The VALUES Clause
A Brief Overview
You can use the VALUES
clause to insert a value for all or only some of the columns in the
table.
|
Desired Result
|
Steps to Take
|
Example
|
|---|---|---|
|
insert a value for all
columns in the table
|
You can omit the optional
list of column names in the INSERT statement.
PROC SQL actions:
|
insert into work.newtable
values ('WI','FLUTE',6)
values ('ST','VIOLIN',3);
|
|
insert a value for only
some of the columns in the table
|
You must include a list
of column names in the INSERT statement.
PROC SQL actions:
|
insert into work.newtable
(item,qty)
values ('FLUTE',6)
values ('VIOLIN',3);
|
You must list a value
for every column into which PROC SQL will insert values (as specified
in either the table or the optional list of column names). To specify
that a value is missing, use a space enclosed in single quotation
marks for character values and a period for numeric values.
For example, the following
VALUES clause specifies values to be inserted in three columns. The
first two values are missing:
values (' ', ., 45)
In the example above,
the first value that is specified is a missing value for a character
column. The second value is a missing value for a numeric column,
and the third value is the numeric
45.VALUES Clause Syntax
The INSERT
statement uses the VALUES clause to insert a list of values into a
table. Unlike the SET clause, the VALUES clause does not specify a
column name for each value, so the values must be listed in the correct
order. Values must be specified in the order in which the columns
appear in the table or, if an optional column list is specified, in
the order in which the columns appear in that list. A separate VALUES
clause is used for each row to be added to the table.
|
Syntax, INSERT statement
containing the VALUES clause:
INSERT INTO table-name <(target-column-1<,
... target-column-n)>
VALUES (value-1<,
... value-n)>
<...
VALUES (value-1<,
... value-n>)>;
table-name
specifies the name
of the table to which rows will be inserted.
target-column
specifies the name
of a column into which data will be inserted.
each VALUES clause
lists the values to
be inserted in some or all columns in one row. The values are enclosed
in parentheses. Multiple VALUES clauses are not separated by commas.
value
specifies a data value
to be added. Character values must be enclosed in quotation marks.
Multiple values must be separated by commas. Values must be listed
in positional order, either as they appear in the table or, if the
optional column list is specified, as they appear in the column list.
|
Note: It is optional to include
a list of target column names after the table name in the INSERT TABLE
statement that includes a VALUES clause. The list can include the
names of all or only a subset of columns in the table. If an optional
list of target column names is specified, then only those columns
are given values by the statement. Target columns can be listed in
any order, regardless of their position in the table. Any columns
that are in the table but not listed are given missing values in the
inserted rows.
Example: Inserting Rows Using the VALUES Clause
Suppose you want to
insert two more rows into the table Work.Discount, which stores airline
discounts for certain flight destinations and time periods in March.
In the previous section, you inserted two rows into Work.Discount
by using the SET clause, so the table now looks like the following
table.
Output 2.3 PROC SQL Query Result: Work.Discount
Add two more rows, by
using the VALUES clause. The following PROC SQL step adds two rows
of new data to Work.Discount and generates a report that displays
the updated table.
proc sql;
insert into work.discount (destination,
begindate,enddate,discount)
values ('ORD', '05MAR2018'd, '15MAR2018'd, .25)
values ('YYZ', '06MAR2018'd, '20MAR2018'd, .10);
select *
from work.discount;
quit;
Output 2.4 PROC SQL Query Result: Inserting Rows
The two rows that were
just inserted by using the VALUES clause are the third and fourth
rows above.
You might have noticed
that the INSERT statement in this example includes an optional list
of column names. In this example, data is being inserted into all
columns of the table, and the values are listed in the order in which
the columns appear in the table. Therefore, it is not strictly necessary
to use a column list. However, including the list of column names
makes it easier to read the code and understand what the code is doing.
The INSERT Statement with SELECT and FROM Clauses
A Brief Overview
The fastest way to insert
rows of data into a table is to use a query to select existing rows
from one or more tables or views and to insert the rows into another
table. You can insert rows from a query result into either an empty
table or a table that already contains rows of data. When you add
rows of data to a table that already contains rows, the new rows are
added at the end of the table.
To insert rows from a query result, use an INSERT statement
that includes the clauses that are used in a query: SELECT, FROM,
and any optional clauses, such as ORDER BY. Values from the query
result are inserted into columns in the order in which the columns
appear in the table or, if an optional column list is specified, in
the order in which the columns appear in that list.
INSERT Statement with SELECT and FROM Clause Syntax
|
Syntax, INSERT statement
containing query clauses:
INSERT INTO table-name <(target-column-1<,
... target-column-n)>
SELECT column-1<,
... column-n>
FROM table-1
| view-1<,
... table-n | view-n>
<optional
query clauses>;
table-name
specifies the name
of the table to which rows will be inserted.
target-column
specifies the name
of a column into which data will be inserted.
SELECT
specifies the columns
that will be inserted.
FROM
specifies the tables
or views to be queried.
optional query clauses
are used to refine
the query further. These include the WHERE, GROUP BY, HAVING, and
ORDER BY clauses.
|
Note: It is optional to include
a list of target column names after the table name in the INSERT TABLE
statement that includes query clauses. The list can include the names
of all or only a subset of columns in the table. If an optional list
of target column names is specified, then only those columns are given
values by the statement. Target columns might be listed in any order,
regardless of their position in the table. Any columns that are in
the table but not listed are given missing values in the inserted
rows.
Example: Inserting Rows from a Query Result
A mechanic at a company
has been promoted from level 2 to level 3, and you need to add this
employee to Certadv.Mechanicslevel3, a table that lists all level-3
mechanics. Create a temporary copy of Certadv.Mechanicslevel3 called
Work.Mechanicslevel3_New, and display the new table in a report:
proc sql;
create table work.mechanicslevel3_new as
select *
from certadv.mechanicslevel3;
quit;
Output 2.5 PROC SQL Query Result: Work.Mechanicslevel3_New
Next, insert a row
into Work.Mechanicslevel3_New for the new level-3 employee, whose
EmpID is 1653. This employee is currently listed in Certadv.Mechanicslevel2,
so your INSERT statement queries the table Certadv.Mechanicslevel2.
Your PROC SQL step ends with a SELECT statement that creates an output
of the revised table Work.Mechanicslevel3_New.
proc sql; insert into work.mechanicslevel3_new select empid, jobcode, salary from certadv.mechanicslevel2 where empid='1653'; select * from work.mechanicslevel3_new; quit;
Output 2.6 PROC SQL Query Result: One Row Inserted
Note: Although the new row is shown
above, the order of rows in a PROC SQL query cannot be guaranteed
if an ORDER BY clause is not used.
Last updated: October 16, 2019
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.