Specifies the number of rows in a single DBMS insert.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Data Set Control |
| Default: | LIBNAME option value |
| Restrictions: | SAS allows the maximum number of rows that the DBMS allows, up to 32,767 rows. |
| 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. | |
| The optimal value for this option varies with factors such as network type and available memory. | |
| DB2 under UNIX and PC Hosts: If one row in the insert buffer fails, all rows in the insert buffer fail. | |
| MySQL: 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. | |
| Interactions: | If you specify DBCOMMIT= with a value that is less than the value of INSERTBUFF=, then DBCOMMIT= overrides INSERTBUFF=. |
| DB2 under UNIX and PC Hosts, Greenplum: To use this option, you must specify INSERT_SQL=YES. | |
| Hadoop, Spark: This option is used only when BULKLOAD=NO. | |
| ODBC to Microsoft SQL Server: If you are using INSERTBUFF=, do not also set BULKLOAD=YES. | |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Snowflake, Spark, Vertica, Yellowbrick |
| Notes: | Support for Yellowbrick was added in SAS 9.4M7. |
| Support for Hadoop, Informix, and Spark was added in SAS 9.4M9. | |
| Tip: | You might need to experiment with different values determine the best value for your site. |
| See: | DBCOMMIT= LIBNAME option, DBCOMMIT= data set option, INSERTBUFF= LIBNAME option, INSERT_SQL LIBNAME option, INSERT_SQL data set option, READBUFF= LIBNAME option, READBUFF= data set option |
Table of Contents
specifies the number of rows to insert. SAS allows the maximum that the DBMS allows.