T-SQL » Statements » SELECT INTO

Syntax:
SELECT field1 [ , field2 [ , ... ] ] INTO newtable
FROM source
field1
Is a parameter that specifies the list of the fields that are to be retrieved.
newtable
Is the name of the table the data is to be sent to.
source
Is the name of the table the data is to be retrieved from.

The SELECT INTO statement is used to create a make-table query. It is especially useful for making backup copies of tables and reports, or for archiving records.

Examples

Code:
SELECT * INTO [Customers Backup]
FROM Customers;
Output:
(5 row(s) affected)
Explanation:

The preceding example makes a backup copy of the Customers table.

Note: The brackets ( [ ] ) are used to allow for the space between the works Customers and Backup. The SELECT INTO statement doesn't define a primary key for the new table, so you may want to do that manually.

Language(s): MS SQL Server
Code:
SELECT Name INTO Fiddlers
FROM Musicians
WHERE Instrument = 'fiddle';
Output:
(2 row(s) affected)
Explanation:

If only a copy of a few fields is needed, you can do so by listing them after the SELECT statement. This query creates a 'Fiddlers' table by extracting the names of fiddlers from the 'MusicArtists' table.

Language(s): MS SQL Server
Code:
SELECT Suppliers.Name, Product, Products.UnitPrice
INTO [Mexican Suppliers]
FROM Suppliers INNER JOIN Products
ON Suppliers.ProductID = Products.ProductID
WHERE Suppliers.Country = 'Mexico';
Output:
(9 row(s) affected)
Explanation:

The fields which are to be copied into a new table need not come from just one table. They can be copied from multiple tables as demonstrated in this example, which selects fields from the two tables 'Suppliers' and 'Products' to create a new table called 'Mexican Suppliers'.

Language(s): MS SQL Server

See Also: