Data Set Options for Relational Databases INSERT_SQL= Data Set Option 193
See Also
To assign this option to a group of relational DBMS tables or views, see the
LIBNAME option “IN= LIBNAME Option” on page 101.
INSERT_SQL= Data Set Option
Determines the method that is used to insert rows into a data source
Valid in:
DATA and PROC steps (when accessing DBMS data using SAS/ACCESS
software)
DBMS support:
ODBC, OLE DB, Microsoft SQL Server
Default value:
LIBNAME setting
Syntax
INSERT_SQL=YES | NO
Syntax Description
YES
specifies that the SAS/ACCESS engine uses the data source’s SQL insert method to
insert new rows into a table.
NO
specifies that the SAS/ACCESS engine uses an alternate (DBMS-specific) method to
add new rows to a table.
Details
Flat file databases (such as dBase, FoxPro, and text files) generally have improved
insert performance when INSERT_SQL=NO, but other databases may have inferior
insert performance (or may fail) with this setting, so you should experiment to
determine the optimal setting for your situation.
ODBC Details: The ODBC default is YES, except for MS Access which has a default
of NO. When INSERT_SQL=NO, the SQLSetPos (SQL_ADD) function inserts rows in
groups that are the size of the INSERTBUFF= option value. The SQLSetPos
(SQL_ADD) function does not work unless it is supported by your ODBC driver.
OLE DB Details: By default, the OLE DB interface attempts to use the most efficient
row insertion method for each data source. You can use the INSERT_SQL option to
override the default in the event that it is not optimal for your situation. The OLE DB
alternate method (used when this option is set to NO) uses the OLE DB
IRowsetChange interface.
SQL Server Details: The SQL Server default is YES. When INSERT_SQL=NO, the
SQLSetPos (SQL_ADD) function inserts rows in groups that are the size of the
INSERTBUFF= option value. The SQLSetPos (SQL_ADD) function does not work
unless it is supported by your ODBC driver.
See Also
To assign this option to a group of relational DBMS tables or views, see the
LIBNAME option “INSERT_SQL= LIBNAME Option” on page 102.
