SQL Select Into
The SQL SELECT INTO Statement
Data from one table is copied into another table using the SELECT INTO statement.
SELECT INTO Syntax
Copy all columns into a new table:
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
Copy only some columns into a new table:
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
The column names and types from the old table will be carried over into the newly constructed table. The AS clause can be used to generate new column names.
SQL SELECT INTO Examples
The following SQL statement creates a backup copy of Customers:
SELECT * INTO CustomersBackup2017
FROM Customers;
The table is copied into a new table in a different database using the IN clause in the following SQL statement:
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;
Only a few columns are copied into a new table by the SQL statement that follows:
SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;
Only the German clients are copied into a new table by the SQL query that follows:
SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';
Data from many tables is copied into a new table using the SQL query that follows:
SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Advice: You may also use SELECT INTO to leverage the schema of another table to create a new, empty table. Simply add a WHERE clause to make the query output null values:
SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;
