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; |
NOTE: 2 row was inserted into WORK.DISCOUNT.
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.
|
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;
NOTE: 2 rows were inserted into WORK.DISCOUNT.
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);
|
values (' ', ., 45)
45
.
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.
|
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;
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.
|
proc sql; create table work.mechanicslevel3_new as select * from certadv.mechanicslevel3; quit;
proc sql; insert into work.mechanicslevel3_new select empid, jobcode, salary from certadv.mechanicslevel2 where empid='1653'; select * from work.mechanicslevel3_new; quit;