SQL SELECT INTO

In this tutorial, we will learn how to use SELECT INTO statement using SQL.

SQL SELECT INTO Statement

SQL SELECT INTO copies data from one table into a new table.
SQL SELECT INTO statement is used to create a table from an existing table by copying the existing table's columns.

Note:- When creating a table using this way, the new table will be populated with the records present in the existing table (based on the SELECT Statement).

Note:- WHERE condition is optional to use along with SELECT INTO Clause.

Note:- When using the SELECT INTO statement in SQL Server, the newTable must not already exist. If it does already exist, the SELECT INTO statement will give an error.

Syntax - Copy all columns into a new table using SELECT INTO Statement

SELECT *
INTO newTable [IN externaldb]
FROM oldTable
WHERE CONDITION;

Syntax - Copy only specified columns into a new table using SELECT INTO statement

SELECT column1,column2,....columnN
INTO newTable [IN externaldb]
FROM oldTable
WHERE CONDITION;

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

ID	EmpName	City	Country	Gender	Salary
1	Shankar	Delhi	India	male	25000
2	Sourabh	Delhi	India	male	30000
3	Ranvijay	Mumbai	India	male	15000
4	Kapil	Noida	India	male	25000
5	Shalini	Jaipur	India	female	18000
6	Rakesh	Faridabad	India	male	23000
7	Akshay	Mumbai	India	male	21000
8	Sarah	New York	US	female	76000
9	Rocky	Noida	India	male	28000

Example of SELECT INTO Statement

For copying records from the old table to the new table using SELECT INTO statement, a query will be:

Example - Copy all columns into a new table using SELECT INTO Statement

SELECT * INTO BackupTable 
FROM Employee

Output

ID	EmpName	City	Country	Gender	Salary
1	Shankar	Delhi	India	male	25000
2	Sourabh	Delhi	India	male	30000
3	Ranvijay	Mumbai	India	male	15000
4	Kapil	Noida	India	male	25000
5	Shalini	Jaipur	India	female	18000
6	Rakesh	Faridabad	India	male	23000
7	Akshay	Mumbai	India	male	21000
8	Sarah	New York	US	female	76000
9	Rocky	Noida	India	male	28000

A new table is created with the name "BackupTable".

For creating a backup copy of the table into a new table in another database using the IN clause, a query will be:

Example

SELECT * INTO EmployeeBackup2018 IN 'Backup.mdb'
FROM Employee;

For creating copy only a few columns into a new table, a query will be:

Example - Copy Only a few Columns into a new table using SELECT INTO

SELECT EmpName,City,Country,Salary INTO EmployeeBackup2018
FROM Employee;

For creating a copy of Indian employee into a new table using WHERE clause, a query will be:

Example

SELECT * INTO EmployeeBackup2018
FROM Employee
WHERE Country='India';

SELECT INTO can also be used for creating a new empty table with the schema of an old table, just add a WHERE clause that causes the statement to return no data, a query will be:

Example - Creating a new empty table with the schema of an old table

SELECT * INTO newTable
FROM oldtable
WHERE 1 = 0;

This query will return no records and just create a copy of a table.