Teradata Insert Multiple Rows

Below is an example of an insert statement with multiple rows in most of the popular RDBMS like Oracle, MSSQL, MySQL, etc.

INSERT INTO Student VALUES
(01,'Pritam','Roy','Comp. Sci.')
(02,'Rahul','Haldar','IT');

Unfortunately, Teradata does not support traditional VALUES with multiple rows in a single statement. Alternatively, you can use Multi Statement Request(MSR)

Syntax

Insert into database_name.tbl_name values (value1, vlaue2)
;Insert into database_name.tbl_name values (value3, vlaue4)
;

Example

Let’s create a volatile table as below.

CREATE VOLATILE TABLE STUDENT
(
roll_no Integer,
FirstName varchar(20),
LastName varchar (20)
) ON COMMIT PRESERVE ROWS;

 *** Table has been created.
 *** Total elapsed time was 2 seconds.

You can use Teradata MSR statements to insert the data into the above volatile table.

INSERT INTO STUDENT VALUES (01,'Derek','Obreain')
;INSERT INTO STUDENT VALUES (02,'Peter','Josh')
;INSERT INTO STUDENT VALUES (03,'Neilson','Pratrick')
;

If you are using Teradata SQL Assistant, you can execute the above insert statements using execute parallel button.

Finally, you can check the records of the table using the SELECT statement.

SELECT * FROM Student;

 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

    roll_no  FirstName             LastName
-----------  --------------------  --------------------
          3  Neilson               Pratrick
          1  Derek                 Obreain
          2  Peter                 Josh