SQL Select Into

The SQL SELECT INTO Statement

The SELECT INTO statement duplicates data from one table to create a new table.

SELECT INTO Syntax

Duplicate all columns into a new table.

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Duplicate select columns into a new table.

SELECT column1, column2, column3, …
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

The new table will inherit the column names and types defined in the original table. You can specify new column names using the AS clause.

SQL SELECT INTO Examples

The following SQL statement generates a backup of the Customers table.

SELECT * INTO CustomersBackup2017
FROM Customers;

This SQL statement utilizes the IN clause to duplicate the table into a new table in another database.

SELECT * INTO CustomersBackup2017 IN ‘Backup.mdb’
FROM Customers;

This SQL statement duplicates only selected columns into a new table.

SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;

This SQL statement duplicates only the German customers into a new table.

SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = ‘Germany’;

This SQL statement duplicates data from multiple tables into a new table.

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Note that SELECT INTO can also be employed to create a new, empty table mirroring the schema of another. Simply include a WHERE clause that ensures the query returns no data.

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;