Insert Into Select statement in Teradata

Insert statement is used to insert a rows in a table. It is providing two option to perform the inserts in Teradata.

• Insert Into Statement – It requires the values in the insert statement. If we try to insert multiple rows, it consumes more time.
• Insert Into Select Statement – It doesn’t requires the values in the statement. Since it has the SELECT statement, it fetches the values from the source table.

In this tutorial, we will discuss about the Insert Into Select statement with examples.

Syntax of Insert into Select statement in Teradata

1) Insert Into Select *

2) Insert Into Select with specific columns

• After the insert into statement, The target database name and its table name should be mentioned.
• The column names of the target table are optional .
• If we want to insert all the columns from the source to target table, we can mention the * (star) after the SELECT clause. otherwise we need to mention each column names with (,)comma separated.
• If we want to perform JOIN condition in the SELECT statement, we need to mention the specific column name after the SELECT clause.

Example 1: Insert Into Select ( with single source table)

The source table customer_details already exist in the Customer_Db database. It contains the both prime and non-prime customer details. This table has a flag called Prime_Active_Flag to differentiate the prime and non-prime customers as below.

• Prime_Active_Flag -> Yes ( prime customer)
• Prime_Active_Flag -> No ( non-prime customer)

Lets create a target table in the same database to illustrate the Insert into select statement.

The above create statement is created the table Prime_customer_details. In this example, we are going to insert the prime customer details from the source table to target table.

• Source table – customer_details
• Target table – Prime_customer_details

To insert the active prime customer details from source to target table, we have written the above INSERT INTO SELECT query with the required WHERE condition.

• The target table name Customer_DB.Prime_customer_details given after the INSERT INTO statement.
• The column names of the source table are mentioned in the SELECT query.
• Next the source table name Customer_DB.customer_details is mentioned
• Finally the WHERE condition are mentioned.

The INSERT INTO SELECT query has executed successfully. Lets check the results in the target table.

As we can see below, the active prime customer details are inserted into the target table Prime_customer_details.

Example 2: Insert Into Select (multiple source tables with JOIN condition)

Lets consider that we have another source table called Customer_address_details. It contains the address of each customer.

To show the usage of multiple source table in the INSERT INTO SELECT statement, lets write the JOIN condition to check the customer_id in both the source tables such as customer_details & Customer_address_details.

The active prime customers are present in both the source tables, As a result, the records are inserted successfully in the target table Prime_customer_details .

Recommended Articles

• Insert Into Statement in Teradata