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

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

SELECT * INTO backUpEmployee from GFG_Employees; 

Output:

Select * from backUpEmployee;

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; 

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; 

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.