SQL SELECT INTO statement

SQL SELECT INTO statement

Sql allows you to copy information from one table to another.

The SELECT INTO statement copies the data from one table and then inserts the data into another new table.

SQL SELECT INTO syntax

We can copy all the columns into the new table:

SELECT *
INTO newtable [IN externaldb]
FROM table1;

Or just copy the desired column and insert it into the new table:

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

	Tip: A new table will be created using the column names and types defined in the SELECT statement. You can use the AS clause to apply a new name.

SQL SELECT INTO instance

Create a backup copy of Customers:

SELECT *
INTO CustomersBackup2013
FROM Customers;

Use the IN clause to copy the table to another database:

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

Only a few columns are copied into the new table:

SELECT CustomerName,
ContactName
INTO CustomersBackup2013
FROM Customers;

Only customers who copy Germany are inserted into the new table:

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

Copy data from multiple tables and insert it into a new table:

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

Tip: S elect INTO statements can be used to create a new empty table in another mode. Simply add the WHERE clause so that the query returns with no data:

SELECT *
INTO newtable
FROM table1
WHERE 1=0;