The SELECT INTO statement is another way to insert data into SQL Server tables. When you use SELECT INTO, SQL Server creates a new table with the specified name in the default filegroup and then inserts the rows from a SELECT query in the newly created table. This new table is based on the columns you specify in the SELECT list, and it must be unique within a database.

To execute the SELECT…INTO statement, a user must have at least the SELECT permission assigned on the target table and the CREATE TABLE permission assigned on the target database. The following is the basic syntax for a SELECT INTO clause:

SELECT [ALL|DISTINCT] select_list
[INTO[[database.]owner.]table_name]
FROM[[[database.]owner.]table_name|view_name|UDF]
[WHERE search_conditions]
[GROUP BY aggregate_free_expression]
[HAVING search_conditions]
[ORDER BY table_or_view_and_column]
[OPTION (query_hint)]

For example, the following T-SQL code uses the SELECT INTO statement to create a backup copy of the Production.Product table:

USE [AdventureWorks2012];
GO

SELECT *
INTO [Production].[Product_Backup]
FROM [Production].[Product];
GO