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

A polyglot and a Big Data enthusiast 専攻はコンピュータ科学です

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.