SQL SELECT INTO

Thursday, December 8, 2011

tags SQL

The SQL SELECT INTO statement can be used to select rows from one table and insert them into another. This is useful when you want to copy or create a backup of a table.

Employees Table

employeeIDemployeeName
1000John Smith
1001Fred White
1002Jane Scott
1003Samuel Williams


In this example, we will select records from the employees table and copy them to the employees_bk table.


Syntax

SELECT *
INTO newTable
FROM oldTable

or

SELECT *
INTO newTable IN 'newDB.mdb'
FROM oldTable

or

SELECT tableName1.columnName1, tableName1.columnName2, etc...
INTO newTable
FROM oldTable
WHERE employeeID > 1002

or

SELECT tableName1.columnName1, tableName1.columnName2, etc...
INTO newTable
FROM oldTable
WHERE employeeID > 1002


Example

Make a copy of all fields in the employees table and insert them into the employees_bk table.

SELECT *
INTO employees_bk
FROM employees


Results

employeeIDemployeeName
1000John Smith
1001Fred White
1002Jane Scott
1003Samuel Williams

The SELECT INTO inserted the rows from the employees table into the employees_bk table.

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

SQL Cookbook Head First SQL: Your Brain on SQL A Learners Guide