MULTISTMT= Data Set Option

Specifies whether insert statements are sent to Teradata one at a time or in a group.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	NO
Restriction:	You cannot currently use MULTISTMT= with the ERRLIMIT= option.
Data source:	Teradata
See:	DBCOMMIT= LIBNAME option, DBCOMMIT= data set option, ERRLIMIT= LIBNAME option, ERRLIMIT= data set option, MULTILOAD= data set option, MULTISTMT= LIBNAME option

Syntax

Details

Examples

Example 1: Send and Insert Statements One at a Time

Example 2: Send 100 Rows at a Time

Example 3: Send a Specified Group of Rows at a Time

Example 4: Use a Global Options to Store a Temporary Table

Syntax

MULTISTMT=YES | NO

Syntax Description

YES: tries to send as many inserts to Teradata that can fit in a 64K buffer. If multistatement inserts are not possible, processing reverts to single-row inserts.

NO: send inserts to Teradata one row at a time.

Details

When you request multistatement inserts, SAS first determines how many insert statements that it can send to Teradata. Several factors determine the actual number of statements that SAS can send—for example:

how many SQL insert statements can fit in a 64K buffer
how many data rows can fit in the 64K data buffer
how many inserts the Teradata server chooses to accept

When you need to insert large volumes of data, you can significantly improve performance by using MULTISTMT= instead of inserting only single-row.

When you also specify the DBCOMMIT= option, SAS determines the number of insert statements to send together at one time. It uses the smaller of the DBCOMMIT= value and the number of insert statements that can fit in a buffer.

Examples

Example 1: Send and Insert Statements One at a Time

This example shows how to send insert statements one at a time to Teradata.

libname user teradata user=zoom pw=XXXXXX server=dbc;
proc datasets library=user;
  delete testdata;run;
data user.testdata(DBTYPE=(I="INT") MULTISTMT=NO);
     do i=1 to 50;
         output;
     end;
run;

Example 2: Send 100 Rows at a Time

In this example, DBCOMMIT=100. Therefore, SAS issues a commit after every 100 rows, sending only 100 rows at a time.

libname user teradata user=zoom pw=XXXXX server=dbc;
proc datasets library=user;
  delete testdata;run;
proc datasets library=user;
  delete testdata;run;
data user.testdata(MULTISTMT=YES DBCOMMIT=100);
do i=1 to 1000;
   output;
   end;
run;

Example 3: Send a Specified Group of Rows at a Time

In this example, DBCOMMIT=1000, which is much higher than in the previous example. SAS sends as many rows as it can fit in the buffer at a time (up to 1000), and it issues a commit after every 1000 rows. If only 600 can fit, 600 are sent to the database. It is followed by the remaining 400—the difference between 1000 and the initial 600 that were already sent. SAS then commits all rows.

libname user teradata user=zoom pw=XXXXX server=dbc;
proc datasets library=user;
  delete testdata;run;
proc datasets library=user;
  delete testdata;run;
data user.testdata(MULTISTMT=YES DBCOMMIT=1000);
do i=1 to 10000;
   output;
   end;
run;

Example 4: Use a Global Options to Store a Temporary Table

This example sets CONNECTION=GLOBAL for all tables, creates a global temporary table, and stores the table in the current database schema.

libname user teradata user=zoom pw=XXXXX server=dbc connection=global;
proc datasets library=user;
  delete temp1;run;

proc sql;
   connect to teradata(user=zoom pw=XXXXXXX server=dbc connection=global);
   execute (CREATE GLOBAL TEMPORARY TABLE temp1 (col1 INT )
            ON COMMIT PRESERVE ROWS) by teradata;
   execute (COMMIT WORK) by teradata;
quit;
data work.test;
  do col1=1 to 1000;
     output;
  end;
run;
proc append data=work.test base=user.temp1(multistmt=yes);
run;