Data Set Options for Relational Databases

INSERT_SQL= Data Set Option

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

Syntax
Syntax Description
Details
See Also

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 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.