Using the SELECT INTO statement to create backup and archive copies of data

This article identifies common uses for the SELECT INTO statement and gives syntax definition and code examples to demonstrate the statements potential uses.

The SELECT INTO statement combines a number of steps of functionality for us into one easy to use statement.

1. It selects a set of records just like a normal SELECT statement would
2. It then creates a new table with the same column definitions as it has just selected
3. It then INSERTS the selected rows into this new table

The syntax is as follows:

SELECT ColumnName[, n] 
INTO NewTableName
FROM SourceTable(s) 
[WHERE ...]

The SELECT INTO statement is especially useful for quickly creating backup copies of data.

The following code example shows how to create a backup copy of an entire table. It selects all columns (*) from the Employees table, creates a new table called EmployeesBackup that has the same column names / data types as the original Employees table and then inserts all the selected records into this new table.

You can also shape the data you copy to the new table by specifying which columns to select and by using the WHERE clause to filter the returned rows. In the following example we are only copying the 4 specified columns into the new table and we are only copying records where the employee is based in the UK.

SELECT Forename, Surname, EmployeeID, StartDate
INTO UKEmployees
WHERE CountryCode = 'UK'

You can also combine data from a number of tables into the new table by using JOINS to join tables just as you would in a standard SELECT statement. This can prove especially useful when you want to archive data so that any future changes to lookup tables do not change the values of the archived data. The following example shows how old products from a sales inventory might be copied to a new archive table.

SELECT Product.*, Category.Title
INTO ArchivedProducts
FROM Product INNER JOIN Category ON Product.CategoryID = Category.CategoryID
WHERE Product.Status = 'Expired' 

Conclusion

In this article we have seen how the SELECT INTO statement enables us to combine selection of data, creation of a new table and insertion of data into one quick and easy to use operation. Common uses include creating backup copies of data and archiving old data. The statement gives us a lot of flexibility in the data that is copied as we can use the syntax of the standard SELECT statement such as the WHERE clause, JOINS, ORDER BY etc to help shape our data.

Discuss this article in the forums

Tell a friend about this page