How to Write Insert and Select in Single Query

SQL query usage depends on the project you work on, some queries are straightforward, and some need your attention. Joins and Sub-queries most people feel hard. But still practicing gives ideas. 

Here are the best examples to use insert and select in a single query:

Query-1

The INTO is the keyword you must use for insert, and the columns you can specify as needed.

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

Query-2

Selecting data from one table, and you can insert into other table in single query.

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

Sample Table

employee

These are three columns present in the employee table.

deptno	name	exp
A1	Vishnu	13
A2	Ravi	5
A3	Mahi	10

Created promotion Table

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

Inserting values from employee table to promotion

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