SQL SELECT INTO Statement

The SELECT INTO statement in SQL is a very useful way to create a new table and populate it with data from an existing table or the results of a query. It's often used for creating backups or duplicating datasets.

SELECT INTO: Definition and Usage

SELECT INTO efficiently copies data into a new table. You can select all columns using `*` or specify individual columns. The new table takes its column names and data types from the source table, unless you use aliases in your `SELECT` statement. You can also filter the copied data using a WHERE clause.

Syntax

-- Copy all columns:
SELECT *
INTO new_table_name [IN external_database_name]
FROM existing_table_name
WHERE condition;

-- Copy specific columns:
SELECT column1, column2, ...
INTO new_table_name [IN external_database_name]
FROM existing_table_name
WHERE condition;
      

The optional IN external_database_name part lets you create the new table in a different database.

Examples

Creating a Full Table Copy

This creates a backup of the 'Customers' table, named 'CustomersBackup2017'.

SELECT * INTO CustomersBackup2017 FROM Customers;
      

(A new table named 'CustomersBackup2017' is created, containing a copy of all data from the 'Customers' table.)
      

Copying to Another Database

Copies 'Customers' to a new table in a different database ('Backup.mdb').

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

(A new table named 'CustomersBackup2017' is created in the 'Backup.mdb' database.)
      

Copying Selected Columns

Copies only 'CustomerName' and 'ContactName' to a new table.

SELECT CustomerName, ContactName INTO CustomersBackup2017 FROM Customers;
      

(A new table named 'CustomersBackup2017' is created with only 'CustomerName' and 'ContactName' columns.)
      

Copying with a WHERE Clause

Copies only German customers.

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

(A new table named 'CustomersGermany' is created containing only German customers.)
      

Copying from Multiple Tables

Copies data from 'Customers' and 'Orders' into a new table (requires a join). This assumes 'Customers' and 'Orders' tables exist with appropriate linking columns.

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

(A new table named 'CustomersOrderBackup2017' is created containing CustomerName and OrderID from a left join.)