Teradata INSERT

After the creation of a table in Teradata, the first table is to insert records into the table. In this tutorial, you will learn how to insert records into a Teradata table using the VALUES keyword.

Teradata INSERT syntax

The basic syntax of inserting records into the Teradata table is as follows.

INSERT INTO database_name.table_name
VALUES (values);

For inserting values into some particular columns only you can use the below syntax.

INSERT INTO database_name.table_name (column1,column2....)
VALUES (values....);

Teradata INSERT example

Let’s create a Student table as follows.

CREATE SET TABLE TUTORIALSBOOK.Student ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      ROLLNO INTEGER,
      FIRSTNAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X
(30)',
      LASTNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      COURSENAME VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
)
UNIQUE PRIMARY INDEX ( ROLLNO );

2) Inserting records into all columns

The below statements insert two records into the table.

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

Let’s check the content of the table using the SELECT statement.

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

     ROLLNO  FIRSTNAME                       LASTNAME                        COURSENAME
-----------  ------------------------------  ------------------------------  --------------------
          1  Pritam                          Roy                             Comp. Sci.
          2  Rahul                           Haldar                          IT

Teradata doesn’t support VALUES with multiple rows. That means the below query will fail.

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

(02,'Rahul','Haldar','IT');
  $
 *** Failure 3707 Syntax error, expected something like ';' between ')' and '('.
                Statement# 1, Info =78
 *** Total elapsed time was 1 second.

2) Inserting records into particular columns

It is also possible to insert records into some particular columns instead of inserting them into all the columns of a table.

The below example shows how to insert a record into some particular column. Note that you must include the NOT NULL columns into your insert list.

INSERT INTO TUTORIALSBOOK.Student (ROLLNO,FIRSTNAME,COURSENAME)
VALUES (03,'Rekha Singh','IT');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

Now let’s check again the table content.

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

     ROLLNO  FIRSTNAME                       LASTNAME                        COURSENAME
-----------  ------------------------------  ------------------------------  --------------------
          1  Pritam                          Roy                             Comp. Sci.
          2  Rahul                           Haldar                          IT
          3  Rekha Singh                     ?                               IT

You can see from the above output that no data was inserted for the LASTNAME whose ROLLNO is 3.

 

Please get connected & share!

Advertisement