JetSQL » Statements » SELECT INTO
- Syntax:
-
SELECT field1 [ , field2 [ , ... ] ] INTO newtable [IN externaldatabase]
FROM source - field1
- Is a parameter that specifies the list of the fields that are to be retrieved.
- newtable
- Is the name of the table the data is to be sent to.
- source
- Is the name of the table the data is to be retrieved from.
The SELECT INTO statement is used to create a make-table query. It is especially useful for making backup copies of tables and reports, or for archiving records.
Examples
Code:
SELECT * INTO [Customers Backup]
FROM Customers;
Output:
(5 row(s) affected)
Explanation:
The preceding example makes a backup copy of the Customers table.
Note: The brackets ( [ ] ) are used to allow for the space between the works Customers and Backup.
The SELECT INTO statement doesn't define a primary key for the new table, so you may want to do that manually.
Language(s): MS SQL Server
Code:
SELECT Name INTO Fiddlers
FROM Musicians
WHERE Instrument = 'fiddle';
Output:
(2 row(s) affected)
Explanation:
If only a copy of a few fields is needed, you can do so by listing them after the SELECT statement. This query creates a 'Fiddlers' table by extracting the names of fiddlers from the 'MusicArtists' table.
Language(s): MS SQL Server
Code:
SELECT Suppliers.Name, Product, Products.UnitPrice
INTO [Mexican Suppliers]
FROM Suppliers INNER JOIN Products
ON Suppliers.ProductID = Products.ProductID
WHERE Suppliers.Country = 'Mexico';
Output:
(9 row(s) affected)
Explanation:
The fields which are to be copied into a new table need not come from just one table. They can be copied from multiple tables as demonstrated in this example, which selects fields from the two tables 'Suppliers' and 'Products' to create a new table called 'Mexican Suppliers'.
Language(s): MS SQL Server
Code:
SELECT Suppliers.* INTO Suppliers IN 'Backup.mdb'
FROM Suppliers; Explanation:
You can also copy tables into another database by using the IN clause.
Language(s): MS SQL Server
See Also:
