INSERTBUFF= Data Set Option

Specifies the number of rows in a single Insert operation.

Valid in: DATA and PROC steps
Default: LIBNAME statement setting
Supports: All

Syntax

INSERTBUFF=positive-integer

Syntax Description

positive-integer

specifies the number of rows to insert.

Details

All data sources default to INSERT_SQL=YES except for SAS data sets. When INSERT_SQL=YES, INSERTBUFF= defaults to 1 and single row inserts are used. The optimal value for this option varies with factors such as network type and available memory. You might need to experiment with the different values to determine the best value for your site.
The SAS application messages that indicate the success or failure of an Insert operation only represent information for 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 specify the DBCOMMIT= option with a value that is less than the value of the INSERTBUFF=, then DBCOMMIT= overrides INSERTBUFF=. If neither DBCOMMIT nor INSERTBUFF is specified, INSERTBUFF defaults to a block size of 32K. SAS determines the number of rows by dividing 32K by the size of each row.
Note: When you insert by using the VIEWTABLE window or the FSVIEW or FSEDIT procedure, use INSERTBUFF=1 to prevent the driver from trying to insert multiple rows. These features do not support inserting more than one row at a time.
Note: Additional driver-specific restrictions might apply.
DB2 under UNIX and PC Hosts Details: You must specify INSERT_SQL=YES in order to use this option. If one row in the insert buffer fails, all rows in the insert buffer fail.
Microsoft SQL Server, Greenplum Details: You must specify INSERT_SQL=YES in order to use this option.