SQL SELECT INTO Statement
The SQL SELECT INTO Statement
The SELECT INTO statement is used to create
a new table and fill it with data from an existing table.
The SELECT INTO statement is useful for
creating backups or for creating a temporary table for analysis.
Note: The new table will be created with the same column names and data types as defined in the source table. However, primary keys, indexes, or NOT NULL constraints are not automatically transferred.
SELECT INTO Syntax
Copy entire table into a new table:
SELECT * INTO newtable [IN external_db]
FROM sourcetable
WHERE condition;
Copy only some columns into a new table:
SELECT column1, column2, column3, ...
INTO newtable [IN external_db]
FROM sourcetable
WHERE condition;
SQL SELECT INTO Examples
The following SQL creates a backup copy of the "Customers" table:
SELECT * INTO CustomersBackup2026
FROM Customers;The following SQL creates a backup copy of the "Customers" table in another database ('Backup.mdb'):
SELECT *
INTO CustomersBackup2026 IN 'Backup.mdb'
FROM Customers;The following SQL copies only a few columns from the "Customers" table into a new table:
SELECT CustomerName, ContactName INTO Customers2
FROM Customers;The following SQL copies only the customers from USA in the "Customers" table, into a new table:
SELECT *
INTO US_Customers
FROM Customers
WHERE Country = 'USA';The following SQL copies data from more than one table into a new table:
SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrder
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Tip: 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 sourcetable
WHERE 1 = 0;
