SELECT INTO statement in SQL

SELECT INTO statement in SQL is generally used for bulk copy purposes. We could copy the whole data from one table into another table using a single command.

Note:
The queries are executed in SQL SERVER and they may not work in many online SQL editors so better use an offline editor.

Syntax:

SELECT column1, column2..... INTO TARGET_TABLE from SOURCE_TABLE 

TARGET_TABLE should have the same schema and data types as that of SOURCE_TABLE.

Let’s first create a table GFG_Employees:

create table GFG_Employees 
 (
  id int,
  name varchar(20),
  email varchar(max),
  department varchar(20)
 ) ;

insert into GFG_EMPLOyees values(1, 'Jessie', 'jessie23@gmail.com', 'Development'); 
insert into GFG_EMPLOyees values(2, 'Praveen', 'praveen_dagger@yahoo.com', 'HR');
insert into GFG_EMPLOyees values(3, 'Bisa', 'dragonBall@gmail.com', 'Sales'); 
insert into GFG_EMPLOyees values(4, 'Rithvik', 'msvv@hotmail.com', 'IT'); 
insert into GFG_EMPLOyees values(5, 'Suraj', 'srjsunny@gmail.com', 'Quality Assurance');
insert into GFG_EMPLOyees values(6, 'Om', 'OmShukla@yahoo.com', 'IT');
insert into GFG_EMPLOyees values(7, 'Naruto', 'uzumaki@konoha.com', 'Development'); 

Id	Name	Email	Department
1	Jessie	jessie23@gmail.com	Development
2	Praveen	praveen_dagger@yahoo.com	HR
3	Bisa	dragonBall@gmail.com	Sales
4	Rithvik	msvv@hotmail.com	IT
5	Suraj	srjsunny@gmail.com	Quality Assurance
6	Om	OmShukla@yahoo.com	IT
7	Naruto	uzumaki@konoha.com	Development

Query-1: To copy all the data from GFG_Employees into backUpEmployee table.

SELECT * INTO backUpEmployee from GFG_Employees; 

Output:

Select * from backUpEmployee;

Id	Name	Email	Department
1	Jessie	jessie23@gmail.com	Development
2	Praveen	praveen_dagger@yahoo.com	HR
3	Bisa	dragonBall@gmail.com	Sales
4	Rithvik	msvv@hotmail.com	IT
5	Suraj	srjsunny@gmail.com	Quality Assurance
6	Om	OmShukla@yahoo.com	IT
7	Naruto	uzumaki@konoha.com	Development

Query-2: Use ‘where’ clause to copy only some rows from GFG_Employees into backUp2 table.

SELECT * INTO backUp2 from GFG_Employees where department in ('Development', 'IT'); 

Output:

Select * from backUp2; 

Id	Name	Email	Department
1	Jessie	jessie23@gmail.com	Development
4	Rithvik	msvv@hotmail.com	IT
6	Om	OmShukla@yahoo.com	IT
7	Naruto	uzumaki@konoha.com	Development

Query-3: To copy only some columns from GFG_Employees into backUp3 table specify them in the query.

SELECT id, name INTO backUp3 from GFG_Employees; 

Output:

Select * from backUp3; 

Id	Name
1	Jessie
2	Praveen
3	Bisa
4	Rithvik
5	Suraj
6	Om
7	Naruto

INSERT INTO SELECT vs SELECT INTO:
Both the statements could be used to copy data from one table to another. But INSERT INTO SELECT could be used only if the target table exists whereas SELECT INTO statement could be used even if the target table doesn’t exist as it creates the target table if it doesn’t exist.

INSERT INTO tempTable select * from GFG_Employees; 

HERE table tempTable should be present or created beforehand else throw an error.

SELECT * INTO backUpTable from GFG_Employees;

Here it’s not necessary to exist before as SELECT INTO creates table if the table doesn’t exist and then copies the data.

My Personal Notes arrow_drop_up

Save