SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to create backup copies of tables.

---

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a different table.
The SELECT INTO statement is most often used to create backup copies of tables.

SQL SELECT INTO Syntax

We can select all columns into the new table:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

Or we can select only the columns we want into the new table:

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

---

SQL SELECT INTO Example

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.
We use the following SQL statement:

SELECT *
INTO Persons_Backup
FROM Persons

We can also use the IN clause to copy the table into another database:

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons

We can also copy only a few fields into the new table:

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

---

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.
The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'

---

SQL SELECT INTO - Joined Tables

Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":

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