Most of us familiar with the Insert Statement by which we insert data into a table. Here, we are using “values” keyword to specify data for the columns.
Insert/Select provide flexibility to insert data into a table by selecting data from one or more tables. 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 inserting all the records of all columns to another table.
INSERT INTO database_name.tbl_2 SELECT * FROM database_name.tbl_1;
Here,
- database_name – The name of the owner database for the table.
- tbl_2 – The name of the table where you want to insert data.
- tbl_1 – The name of the table from where you want to select the data.
Below is the syntax for selecting and inserting selected columns from one or more tables to another table.
INSERT INTO database_name.tbl_2 SELECT (column_1,column_2,..,column_n) from database_name.tbl_2;
OR
INSERT INTO database_name.tbl_2 (column_1,column_2,..,column_n) SELECT (column_1,column_2,..,column_n) from database_name.tbl_2;
You can use aggregate functions, subqueries, expressions also in the SELECT statement but remember that the datatypes of the source and target columns must be the same.