SQL Server Select into statement with example

The SELECT INTO statement creates a new table and inserts rows from the query into it.

If you want to copy the partial data from the source table, you use the WHERE clause to specify which rows to copy. Similarly, you can specify which columns from the the source table to copy to the destination table by specifying them in the select list.

Note that SELECT INTO statement does not copy constraints such as primary key and indexes from the source table to the destination table.

Syntax:

SELECT Column1, Column2, Column3
INTO MyNewTable
FROM MySourceTable;

Let's see with below examples:

• Create a backup copy of Customers:

SELECT * INTO Customers2017
FROM Customers;

• Copy records from one database to another.

SELECT * INTO Rohit.dbo.PurchaseOrderDetail  
FROM AdventureWorks2017.purchasing.PurchaseOrderDetail

• Copy only a few columns into a new table:

SELECT CustomerName, ContactName INTO Customers2017
FROM Customers;

• Copies only the US customers into a new table:

SELECT * INTO CustomersUS
FROM Customers
WHERE Country = 'US';

SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data.

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;