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='01MAR2000'd, enddate='05MAR2000'd, discount=.33 set destination='CPH', begindate='03MAR2000'd, enddate='10MAR2000'd, discount=.15; |
insert lists of values
by using the VALUES clause
|
proc sql; insert into work.discount (destination, begindate,enddate,discount) values ('LHR','01MAR2000'd, '05MAR2000'd,.33) values ('CPH','03MAR2000'd, '10MAR2000'd,.15); |
insert rows that are
copied from another table by using a query result
|
proc sql; insert into payrollchanges2 select empid,salary,dateofhire from sasuser.payrollmaster where empid in ('1919','1350','1401'), |
General form, 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 are inserted.
target-column
specifies the name
of a column into which data is 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 is 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: SQL table WORK.DISCOUNT was created like: create table WORK.DISCOUNT( bufsize=4096 ) ( Destination char(3), BeginDate num format=DATE9., EndDate num format=DATE9., Discount num ); |
General form, 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 are inserted.
target-column
specifies the name
of a column into which data is inserted.
each VALUES clause
lists the values to
be inserted in some or all columns in one row, which is 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.
|
If you want to ...
|
Then ...
|
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
|
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
|
insert into work.newtable
(item,qty)
values ('FLUTE',6)
values ('VIOLIN',3); |
values (' ', ., 45)
proc sql; insert into work.discount (destination, begindate,enddate,discount) values ('ORD','05MAR2000'd,'15MAR2000'd,.25) values ('YYZ','06MAR2000'd,'20MAR2000'd,.10); select * from work.discount;
General form, 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 are inserted.
target-column
specifies the name
of a column into which data is inserted.
SELECT
specifies the column(s)
that is inserted.
FROM
specifies the table(s)
or view(s) to be queried.
optional query clauses
are used to refine
the query further. These include the WHERE, GROUP BY, and HAVING,
clauses.
|
1653
. This employee is currently
listed in Sasuser.Mechanicslevel2, so your INSERT statement queries
the table Sasuser.Mechanicslevel2. Your PROC SQL step ends with a
SELECT statement that outputs the revised table Work.Mechanicslevel3_New
to a report.
proc sql; insert into work.mechanicslevel3_new select empid, jobcode, salary from sasuser.mechanicslevel2 where empid='1653'; select * from work.mechanicslevel3_new; |
|