Using Insert Mode in Teradata

Database
Enthusiast

Using Insert Mode in Teradata

What is the difference between using

1) Insert into table1
sel * from table2
;Insert into table1 ;

Instead of

2) Insert into table1
sel * from table2;
Insert into table1 ;

Whether there will be a difference of no. of records inserted by both the scripts ?

Thanks a lot for your answers!!!
5 REPLIES
Senior Apprentice

Re: Using Insert Mode in Teradata

Hi Ansh,
#1 is one multi-statement request, if it's submitted using BTEQ, whereas #2 are two request.

The number of rows inserted will be the same (unless there's an error).

Check the manuals for details:
SQL Reference: Statement and Transaction Processing
Chapter 8: Locking and Transaction Processing
Transactions, Requests, and Statements

Dieter
Enthusiast

Re: Using Insert Mode in Teradata

Hi Dieter,

Thanks for your response.
So which one of the two do you think will be used if performance is considered and why ?
Enthusiast

Re: Using Insert Mode in Teradata

I think, the first method would be best in terms of performence as there will only one spool file for both the insert statements
Senior Apprentice

Re: Using Insert Mode in Teradata

Hi Ansh,
an insert/select into an empty table might be a so-called "fast-path" insert/select, if the target table is empty at the beginning of the transaction.

If you run tose statements in Teradata session mode, then each request is auto-commited.
Thus the multistatement will be more efficient, because there will be no Transient Journal for both inserts.
#2 needs a TJ for the 2nd insert.

Dieter
Enthusiast

Re: Using Insert Mode in Teradata

Thanks a lot Dieter for your helpful comments.