SQL SELECT INTO Statement

The SELECT INTO statement in SQL is a convenient way to create a new table and populate it with data from an existing table or the results of a query. It's a powerful tool for data replication and creating backups.

SELECT INTO: Definition and Usage

SELECT INTO efficiently copies data from one table into a new table. You can copy all columns or just selected columns. The new table inherits the data types from the source table's columns. You can also apply a WHERE clause to filter the data being copied. Crucially, the data types of the source and destination columns must be compatible.

Syntax

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

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

The IN external_database part is used to create the new table in a different database. The `WHERE` clause allows for selective copying.

Examples

Creating a Table Backup

This creates a complete backup copy 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

This copies the 'Customers' table to a different database named 'Backup.mdb'.

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

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

Copying Specific Columns

This 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 and their data from the 'Customers' table.)
      

Copying with a WHERE Clause

This copies only German customers into a new table.

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

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

Copying from Multiple Tables

This copies data from 'Customers' and 'Orders' into a new table (requires a join condition).

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 based on the left join.)
      

Creating an Empty Table with Schema

This creates an empty table using the structure of another table.

SELECT * INTO newtable FROM oldtable WHERE 1 = 0;
      

(An empty table named 'newtable' is created with the same structure as 'oldtable'.)