Search content within the blog

Friday, April 17, 2009

Select Into in SQlServer

To create a make-table query means for making backup copies of tables
and reports, or for archiving records.

The SELECT...INTO statement doesn't define a primary key for the new table, so you may want to do that manually.

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source

Make a Backup Copy

The example given below makes a backup copy of the "Persons" table:

SELECT * INTO [Customers Backup] FROM Customers;

The IN clause may be used to copy tables into another database:

SELECT Suppliers.* INTO Suppliers IN 'Backup.mdb' FROM Suppliers;

If you only want to copy a few fields not copy whole Table, you can do so by listing them after the SELECT statement. The following query creates a Fiddlers table by extracting the names of fiddlers from a Musicians table:

SELECT Name INTO Fiddlers
FROM Musicians
WHERE Instrument = 'fiddle';

The fields which you copy into a new table need not come from just one table. You can copy from multiple tables as demonstrated in the next example which selects fields from the two tables Suppliers and Products to create a new table for Mexican Suppliers:

SELECT Suppliers.Name, Product, Products.UnitPrice
INTO [Mexican Suppliers]
FROM Suppliers INNER JOIN Products
ON Suppliers.ProductID = Products.ProductID
WHERE Suppliers.Country = 'Mexico';

No comments:

Post a Comment