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. 

Will: The Sunday Times Bestselling Autobiography

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);
How to Write Insert and Select in Single Query
Photo by Karolina Grabowska on Pexels.com

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.

deptnonameexp
A1Vishnu13
A2Ravi5
A3Mahi10

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

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.