The SQL SELECT INTO Statement

SELECT INTO Syntax:

Copy all columns into a new table:

SELECT *
INTO newtable [ IN externaldb]
FROM oldtable
WHERE condition;

Copy only some columns into a new table:

SELECT column1,column2,column3...
INTO newtable [ IN externaldb]
FROM oldtable
WHERE condition;

The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.

SQL SELECT INTO Examples

The following SQL statement creates a backup copy of employee:

SELECT * INTO employeeCopy
FROM employee;

The following SQL statement uses the IN clause to copy the table into a new table in another database:

SELECT * INTO employeeCopy IN 'Copy.mdb'
FROM employee;

The following SQL statement copies only a few columns into a new table:

SELECT Employee_name,Address INTO employeeCopy
FROM employee;

The following SQL statement copies only the Indian employee into a new table:

SELECT * INTO employeeIndia FROM employee
WHERE Country = 'India' ;

The following SQL statement copies data from more than one table into a new table:

SELECT employee.Employee_name,products.Products_id INTO employee_product_Copy
FROM employee
LEFT JOIN products ON employee.Employee_id = products.Employee_id;

Tip: SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0 ;