The Sample Query: Select and Insert INTO

Here’s a way to SELECT and insert INTO a new table.

Select and Insert into the syntax

In the INSERT use the INTO keyword and columns as needed.

INSERT INTO my_table1 (a_column1, b_column2, c_column3)
(
SELECT a_column1, b_column2, c_column3
FROM my_table2
WHERE condition
);

Sample SQL Query: Selecting data from one table, you can insert it into another table in a single query.

INSERT INTO promotion (deptnum, empname, yearsexp) 
(SELECT deptno, name, exp 
FROM employee 
WHERE exp > 10) ;

Employee table

Three columns are present in the employee table. And three rows of data are present in it.

deptnonameexp
A1Vishnu13
A2Ravi5
A3Mahi10

Promotion Table

Create Table promotion
(deptnum char(05),
empname char(20),
yearsexp number);

Here’s how to get rows from the employee table and insert them into the promotion table

The below query writes only one row to the promotion table.

INSERT INTO promotion (deptnum, empname, yearsexp) 
(SELECT deptno, name, exp 
FROM employee 
WHERE exp > 10) ;

Related

Posted in SQL

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.