INSERT-SELECT in Teradata

Teradata INSERT-SELECT statement is used to insert the records into a table from one or more tables. Most of us are familiar with the INSERT statement where we are providing data for the columns after the VALUES keyword.

With the flexibility of insert-select, data can be fetched from a large number of existing tables and inserted into a new table. The SELECT statement reads the data from one or more tables and uses them as the values to insert into another table. Simply put the SELECT query in the place of values portion of the Insert statement.

INSERT-SELECT in Teradata syntax

Below is the syntax for selecting all the records from one table and inserting them into another table.

INSERT INTO database_name.table_2
SELECT * FROM database_name.table_1;

Here,

  • database_name – The name of the owner database for the table.
  • table_2 – The name of the destination table where you want to insert data.
  • table_1 – The name of the source table from where you want to select the data.

Below is the syntax for selecting and inserting particular columns from one or more tables to another table.

INSERT INTO database_name.table_2
SELECT (column_1,column_2,..,column_n) from database_name.table_1;

OR

INSERT INTO database_name.table_2 (column_1,column_2,..,column_n)
SELECT (column_1,column_2,..,column_n) from database_name.table_1;

You can use aggregate functions, subqueries, and expressions also in the SELECT statement but remember that the datatypes of the source and target columns must be the same.

INSERT-SELECT in Teradata example

Let’s understand the concept of the INSERT-SELECT in Teradata with the help of some examples.

Consider the below employee table.

INSERT-SELECT IN TERADATA

Let’s create another table named EmplyeeDesig with the following details.

CREATE SET TABLE TUTORIALSBOOK.EmployeeDesig
     (
      EMPNO INTEGER NOT NULL,
      FIRST_NAME CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      LAST_NAME CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      DESIGNATION VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC
)
UNIQUE PRIMARY INDEX ( EMPNO );

 *** Table has been created.
 *** Total elapsed time was 1 second.

Now with the help of the INSERT-SELECT statement, we will insert records from the Employee table to the EmployeeDesig table.

INSERT INTO TUTORIALSBOOK.EmployeeDesig
SELECT EMPNO,FIRST_NAME,LAST_NAME,DESIGNATION
FROM TUTORIALSBOOK.Employee;

 *** Insert completed. 8 rows added.
 *** Total elapsed time was 1 second.

Finally, let’s check the content of the new EmployeeDesig table using the SELECT statement.

SELECT * FROM TUTORIALSBOOK.EmployeeDesig;

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

      EMPNO  FIRST_NAME                 LAST_NAME                  DESIGNATION
-----------  -------------------------  -------------------------  --------------------
       1008  JOHN                       SMITH                      SALESMAN
       1004  JONES                      NICK                       HR ANALYST
       1006  ISSAC                      STEFAN                     TRAINEE
       1002  DIANA                      LORANCE                    TECHNICAL ARCHITECT
       1007  NANCY                      GILBERT                    CLERK
       1003  JAMES                      MADISON                    MANAGER
       1005  LUCY                       GILLER                     HR ASSOCIATE
       1001  STEFAN                     SALVATORE                  BUSINESS ANALYST

Please get connected & share!

Advertisement