SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to create a backup copy of a table.

SELECT INTO statement

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

The SELECT INTO statement is commonly used to create a backup copy of a table or to archive records.

SQL SELECT INTO syntax

You can insert all columns into the new table:

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

Or only insert the desired columns into the new table:

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

SQL SELECT INTO Example - Creating a Backup Copy

The following example will create a backup copy of the "Persons" table:

SELECT *
INTO Persons_backup
FROM Persons

The IN clause can be used to copy a table to another database:

SELECT *
INTO Persons IN "Backup.mdb"
FROM Persons

If we want to copy certain fields, we can list these fields after the SELECT statement:

SELECT LastName,FirstName
INTO Persons_backup
FROM Persons

SQL SELECT INTO Example - WITH WHERE Clause

We can also add a WHERE clause.

The following example creates a table named "Persons_backup" with two columns by extracting information about people living in "Beijing" from the "Persons" table:

SELECT LastName,Firstname
INTO Persons_backup
FROM Persons
WHERE City='Beijing'

SQL SELECT INTO Example - Connected Tables

It is also possible to select data from more than one table.

The following example will create a new table named "Persons_Order_Backup" that contains information obtained from the Persons and Orders tables:

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P