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. 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
Related Posts:
