The LIBNAME Statement for Relational Databases

INSERT_SQL= LIBNAME Option

Determines the method to use to insert rows into a data source.

Default value:	DBMS-specific, see the details in this section
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	Microsoft SQL Server, ODBC, OLE DB

Syntax
Syntax Description
Details
See Also

Syntax

INSERT_SQL=YES | NO

Syntax Description

YES: specifies that SAS/ACCESS uses the data source's SQL insert method to insert new rows into a table.
NO: specifies that SAS/ACCESS uses an alternate (DBMS-specific) method to insert new rows into a table.

Details

Flat file databases (such as dBASE, FoxPro, and text files) generally have improved insert performance when INSERT_SQL=NO. Other databases might have inferior insert performance (or might fail) with this setting, so you should experiment to determine the optimal setting for your situation.

HP Neoview: The default is YES.

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 it is supported by your driver.

Netezza: The default is YES.

ODBC: The default 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 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.