The LIBNAME Statement for Relational Databases

INSERTBUFF= LIBNAME Option

Specifies the number of rows in a single DBMS insert.

Default value:	DBMS-specific
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase IQ

Syntax
Syntax Description
Details
See Also

Syntax

INSERTBUFF=positive-integer

Syntax Description

positive-integer: specifies the number of rows to insert.

Details

SAS allows the maximum number of rows that the DBMS allows. The optimal value for this option varies with factors such as network type and available memory. You might need to experiment with different values in order to determine the best value for your site.

SAS application messages that indicate the success or failure of an insert operation represent information for only a single insert, even when multiple inserts are performed. Therefore, when you assign a value that is greater than INSERTBUFF=1, these messages might be incorrect.

If you set the DBCOMMIT= option with a value that is less than the value of INSERTBUFF=, then DBCOMMIT= overrides INSERTBUFF=.

When you insert rows with the VIEWTABLE window or the FSVIEW or FSEDIT procedure, use INSERTBUFF=1 to prevent the DBMS interface from trying to insert multiple rows. These features do not support inserting more than one row at a time.

Additional driver-specific restrictions might apply.

DB2 under UNIX and PC Hosts: Before you can use this option, you must first set INSERT_SQL=YES. If one row in the insert buffer fails, all rows in the insert buffer fail. The default is calculated based on the row length of your data.

HP Neoview and Netezza: The default is automatically calculated based on row length.

Microsoft SQL Server: Before you can use this option, you must first set INSERT_SQL=YES. The default is 1.

MySQL: The default is 0. Values greater than 0 activate the INSERTBUFF= option, and the engine calculates how many rows it can insert at one time, based on the row size. If one row in the insert buffer fails, all rows in the insert buffer might fail, depending on your storage type.

ODBC: The default is 1.

OLE DB: The default is 1.

Oracle: When REREAD_EXPOSURE=YES, the (forced) default value is 1. Otherwise, the default is 10.