SQLSELECT INTO Statement

The SQL SELECT INTO Statement

TheSELECT INTO statement copies data from one table into a new table.

SELECT INTO Syntax

Copy all columns into a new table:

SELECT *
INTOnewtable [INexternaldb]
FROMoldtable
WHEREcondition;

Copy only some columns into a new table:

SELECTcolumn1,column2,column3, ...
INTOnewtable [INexternaldb]
FROMoldtable
WHEREcondition;

The new table will be created with the column-names and types as defined in the old table.You can create new column names using theAS clause.

SQL SELECT INTO Examples

The following SQL statement creates a backup copy of Customers:

SELECT * INTO CustomersBackup2017
FROM Customers;

The following SQL statement uses theIN clause to copy the table into a new table in another database:

SELECT *INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

The following SQL statement copies only a few columns into a new table:

SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;

The following SQL statement copies only the German customers into a new table:

SELECT *INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';

The following SQL statement copies data from more than one table into a new table:

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
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 aWHERE clause that causes the query to return no data:

SELECT * INTOnewtable
FROMoldtable
WHERE 1 = 0;

❮ Previous Next ❯

★+1