A SERVICE OF

logo

How SAS/ACCESS Works How the DBLOAD Procedure Works 53
When rows are added to a table, SAS constructs a SQL INSERT statement and
passes it to the DBMS. When you reference a view descriptor, you can use the
ADD command in FSEDIT and FSVIEW, the APPEND procedure, or an INSERT
statement in PROC SQL to add data to a DBMS table. (You can also use the SQL
Procedure Pass-Through Facility’s EXECUTE statement to add, delete, or modify
DBMS data directly. Literal values must be used when inserting data with the
Pass-Through Facility.)
When rows are deleted from a DBMS table, SAS constructs a SQL DELETE
statement and passes it to the DBMS. When you reference a view descriptor, you
can use the DELETE command in FSEDIT and FSVIEW or a DELETE statement
in PROC SQL to delete rows from a DBMS table.
When data in the rows is modified, SAS constructs a SQL UPDATE statement and
passes it to the DBMS. When you reference a view descriptor, you can use
FSEDIT, the MODIFY command in FSVIEW, or an INSERT statement in PROC
SQL to update data in a DBMS table. You can also reference a view descriptor in
the DATA step’s UPDATE, MODIFY, and REPLACE statements.
SAS closes the connection with the DBMS.
How the DBLOAD Procedure Works
When you use the DBLOAD procedure to create a DBMS table, the procedure issues
dynamic SQL statements to create the table and insert data from a SAS data file,
DATA step view, PROC SQL view, or view descriptor into the table.
The SAS/ACCESS interface view engine completes the following steps:
1 When you supply the connection information to PROC DBLOAD, the
SAS/ACCESS interface calls the DBMS to connect to the database.
2
SAS uses the information that is provided by the DBLOAD procedure to construct
a SELECT * FROM table-name statement, and passes the information to the
DBMS to determine if the table already exists. PROC DBLOAD continues only if a
table with that name does not exist, unless you use the DBLOAD APPEND option.
3 SAS uses the information that is provided by the DBLOAD procedure to construct
a SQL CREATE TABLE statement and passes it to the DBMS.
4 SAS constructs a SQL INSERT statement for the current observation and passes
it to the DBMS. New INSERT statements are constructed and then executed
repeatedly until all of the observations from the input SAS data set are passed to
the DBMS. Some DBMSs have a bulkcopy capability that allows a group of
observations to be inserted at once. See your DBMS documentation to determine if
your DBMS has this capability.
5 Additional nonquery SQL statements specified in the DBLOAD procedure are
executed as submitted by the user. The DBMS returns an error message if a
statement does not execute successfully.
6
SAS closes the connection with the DBMS.