Skip to content
Related Articles

Related Articles

Improve Article
SELECT INTO statement in SQL
  • Last Updated : 13 Aug, 2019

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'); 

IdNameEmailDepartment
1Jessiejessie23@gmail.comDevelopment
2Praveenpraveen_dagger@yahoo.comHR
3BisadragonBall@gmail.comSales
4Rithvikmsvv@hotmail.comIT
5Surajsrjsunny@gmail.comQuality Assurance
6OmOmShukla@yahoo.comIT
7Narutouzumaki@konoha.comDevelopment

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

SELECT * INTO backUpEmployee from GFG_Employees; 

Output:

Select * from backUpEmployee;

IdNameEmailDepartment
1Jessiejessie23@gmail.comDevelopment
2Praveenpraveen_dagger@yahoo.comHR
3BisadragonBall@gmail.comSales
4Rithvikmsvv@hotmail.comIT
5Surajsrjsunny@gmail.comQuality Assurance
6OmOmShukla@yahoo.comIT
7Narutouzumaki@konoha.comDevelopment

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; 

IdNameEmailDepartment
1Jessiejessie23@gmail.comDevelopment
4Rithvikmsvv@hotmail.comIT
6OmOmShukla@yahoo.comIT
7Narutouzumaki@konoha.comDevelopment

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; 

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

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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :