Data Set Options for Relational Databases

INSERTBUFF= Data Set Option

Specifies the number of rows in a single DBMS insert.

Default value:	LIBNAME setting
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
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. SAS allows the maximum that the DBMS allows.

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 FSEDIT or FSVIEW procedure, use INSERTBUFF=1 to prevent the engine 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: To 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, Netezza: The default is automatically calculated based on row length.

Microsoft SQL Server, Greenplum: To use this option, you must set INSERT_SQL=YES.

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