Data Set Options for Relational Databases |
Determines the method to use to insert rows into a data source.
Default value: |
LIBNAME setting
|
Valid in: |
DATA and PROC steps (when accessing DBMS data
using SAS/ACCESS software)
|
DBMS support: |
Microsoft SQL Server, ODBC, OLE DB
|
-
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.
Flat-file databases such as dBase, FoxPro,
and text files have generally improved insert performance when INSERT_SQL=NO.
Other databases might have inferior insert performance or might fail with
this setting. Therefore, you should experiment to determine the optimal setting
for your situation.
Microsoft SQL Server: The Microsoft 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 your ODBC driver supports
it.
ODBC: The default for ODBC is YES, except
for Microsoft 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
your ODBC driver supports it.
OLE DB: 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.
To assign this option to a group of
relational
DBMS tables or views, see the
INSERT_SQL= LIBNAME Option.
INSERTBUFF= Data Set Option
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.