Thursday, December 12

INSERT INTO SELECT - SQL

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.

Syntax:

INSERT INTO Table_A         --here it will insert records into Table_A from Table_B, if
SELECT * FROM TABLE_B;      data types are same.

If you want to insert from a specified column of a table to specified column of another table then we can write query as:

INSERT INTO Table_A (column_names) 
SELECT (column_names) FROM TABLE_B;

Example: Consider Table employee_A having same structure as Table employee. 

INSERT INTO employee_A
SELECT * FROM  employee
;

Output:





























Now Consider inserting from a specified column of a table to specified column of another table.

INSERT INTO employee_A (E_ID,E_Name)
SELECT ID,Name FROM employee;

Output:

























Here you could see that only two columns inserted from employee table.


Now consider using where condition along with select statement.

INSERT INTO employee_A (E_ID,E_Name)
SELECT ID,Name FROM employee 
Where Salary is null and Gender is null;

Output:





















You could see that only two such records exists in employee table which is inserted into employee_A table.