The SQL SELECT INTO Statement
The SELECT INTO statement selects data from one table, creates a new table with the exact structure and size and inserts automatically the selected data into the new table.
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 |