Insert Into with Select SQL: How to Write SQL Query

When working with SQL databases, the “Insert Into with Select” statement offers a powerful way to select data from one table and insert it into another in a single query. This efficient syntax eliminates the need for multiple steps and provides a streamlined approach to database operations. Let’s take a closer look at how to utilize this functionality for seamless data manipulation and transfer.

Table of contents

1. Insert into with Select
2. SQL Query to Use Insert into with Select
3. Conclusion

Insert into with Select

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.

Promotion Table

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

SQL Query to Use Insert into with Select

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) ;

Conclusion

The “Insert Into with Select” statement in SQL provides a powerful and efficient way to transfer and manipulate data between tables in a single query. By eliminating the need for multiple steps, this streamlined approach enhances the overall database operation process.