Insert into Teradata

Intorduction to Insert into Teradata

The Insert into statement is utilized to accommodate a new row or record into the table. During the execution of this statement, No records are returned to the user except the status mentioning something like ‘x’ records have been inserted successfully. In this topic, we are going to learn about Insert into Teradata. It accounts for all the values in the table, may it be a NULL or some data value.

An insert statement consists of a list of columns and values to be inserted into those columns. If we are specifying the list of values as per the data structure of the table then the list of columns can be skipped and directly the values to be inserted can be specified.

What is Insert Into in Teradata?

Let’s have a look at the syntax that insert into follows with Teradata:

INSERT INTO table_name (col1, col2, col3, col4, col5, col6, col7)
VALUES ( 1, 'EDU', 'CBA', 5, 5, 5.5, 7);

The above syntax is as per the ANSI syntax that works quite well with Teradata.

• It consists of two keywords, INSERT INTO & VALUES
• table_nameis the name of the table in which the row needs to be inserted
• table_name is followed by the list of columns to which the values need to be inserted
• The keyword VALUES is followed by the actual values that need to be inserted into the table

We can skip mentioning the column names specifically as well if we are mentioning the column values in the specific order as per the order of columns in the table structure.

Let’s take into consideration the below table with 6 columns having the mentioned data types, along with attributes defined.

The alternative where column names can be skipped is as below:

INSERT INTO table_name VALUES( 'edu' , 3.2 , 4 , , NULL , '21-01-2020' )

Once we execute the above Insert Into statement, there will be a new row in the table with

• column1 having the value as edu
• column2 having the value as 3.2
• column 3 having the value as 4
• and so on until column 6 having the date value as 21-01-2020

Whereas Column 4 is having a missing value out there in the Insert into the statement, so column 4 will also have the missing data

And column5 will have Null as specified in the Insert into statement above.

Let’s discuss the same taking up the trivial syntax:

INSERT INTO table_name (column1 , column2 , column6 , column3 )
VALUES ( 'edu' , 3.2 , '21-01-2020' , 4)

Here we have specified the column name explicitly along with the corresponding values for each column

• Column1 will have the value edu
• column2 will have the value 3.2
• Column6 is the next one in the list that’s why it will have the value of 21-01-2020 as date
• Whereas column3 will have the value as 4

Now as we have not even specified in this insert into the statement, column4, and column5.

What will column4 and column5 be holding?

Yes, The missing columns will be assumed as Null and will hold Null values after this statement is executed.

This is generally a good format when the data in the files are not coming in the specified order and does not matches the order of the Table columns

How to Insert in Teradata?

There are multiple ways in which we can have the Insert statement written for different requirements. Let’s Discuss some of them down below:

Case #1

We want to insert multiple records using the Insert Statement

Let’s take an example to understand the same:

INSERT INTO table_name
VALUES( 'edu' , 3.2 , 4 , , NULL , '21-01-2020' ),
( 'CBA' , 4.2 , 5 , , NULL , '22-01-2020' ),
( 'Learn' , 5.2 , 7 , , NULL , '24-01-2020' ),
( 'Online' , 6.2 , 8 , , NULL , '25-01-2020' ),
( 'data' , 8.2 , 9 , , NULL , '27-01-2020' ),
( 'science' , 9.2 , 99 , , NULL , '28-01-2020' ),
( 'Teradata' , 5.2 , 7 , , NULL , '24-01-2020' ),
( 'Python' , 6.2 , 8 , , NULL , '25-01-2020' ),
( 'Tableau' , 8.2 , 9 , , NULL , '27-01-2020' ),
( 'stats' , 9.2 , 99 , , NULL , '28-01-2020' )

The above statement will interest 10 rows to the table having the above-mentioned values.

When we are trying to insert multiple rows using a single insert statement, then each new row in the Insert into statement is separated by a comma.

If in case this comma is missed, Teradata will throw an exception or specifically a syntax error.

Case #2

We want to insert multiple records using the Insert Statement, but the records are coming from a separate table

Let’s take an example to understand the same:

INSERT INTO table_name
SELECT * FROM other_table

The syntax for Insert part is similar to what we have seen so far, but the values section is replaced by the ‘SELECT’ statement

Here this Select statement will be providing the values indirectly and the same will be inserted into the table.

• The select statement selection the values from the other_table should have the same order of columns as expected by the table structure of the table the values are getting inserted into
• Moreover, the data type needs to be the same as well
• If in case the column order isn’t the same then we can either specifically mention the column names in the insert statement like below:
• INSERT INTO table_name (column1 , column2 , column6 , column3 ) SELECT * FROM other_table
• Or can select specific columns from the select statement itself like below:
• INSERT INTO table_name SELECT column1 , column2 , column6 , column3 FROM other_table

This will serve as a workaround in such scenarios.

Conclusion

• INSERT INTO statement is used to insert new records into the Teradata tables
• We can specifically mention the column names if the values to be entered are in a different order as compared to that of the column order of the table
• We can enter multiple records at the same time as well
• A separate table or a file can also be used as the source for these values to be inserted

Recommended Articles

This is a guide to Insert into Teradata. Here we discuss what is Insert into Teradata and multiple ways in which we can have the Insert statement written for different requirements. You may also look at the following articles to learn more –

1. Teradata Architecture
2. Teradata Partition by
3. Primary Index in Teradata
4. What is Teradata?