INSERT_SQL= LIBNAME Option

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

Valid in:	SAS/ACCESS LIBNAME statement
Default:	DBMS-specific
Data source:	Microsoft SQL Server, ODBC, OLE DB
See:	INSERT_SQL= data set option, INSERTBUFF= LIBNAME option, INSERTBUFF= data set option

Syntax

Details

Syntax

INSERT_SQL=YES | NO

Syntax Description

YES: specifies that SAS/ACCESS uses the SQL insert method for the data source 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. You should therefore experiment to determine the optimal setting to meet your needs.

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 your driver supports it.

Netezza: The default is YES.

ODBC: The default is YES, except for Microsoft Access, where the default is 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 tries 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. Used when this option is set to NO, the alternate OLE DB method uses the OLE DB IRowsetChange interface.