The SQL SELECT INTO Statement: A Comprehensive Guide

The SELECT INTO statement copies data from one table into a new table.

Syntax:

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln">
INTO newtable </span><span class="pun">[</span><span class="pln">IN externaldb</span><span class="pun">]</span><span class="pln">
FROM oldtable
WHERE condition</span><span class="pun">;</span><span class="pln">
</span>

Copy only some columns into a new table:

Syntax:

<span class="pln">
SELECT column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> column3</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...</span><span class="pln">
INTO newtable </span><span class="pun">[</span><span class="pln">IN externaldb</span><span class="pun">]</span><span class="pln">
FROM oldtable
WHERE condition</span><span class="pun">;</span><span class="pln">
</span>

SQL SELECT INTO Examples

The following SQL statement creates a backup copy of Employee:

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> INTO </span><span class="typ">EmployeeBackup1</span><span class="pln">
FROM </span><span class="typ">Employee</span><span class="pun">;</span><span class="pln">
</span>

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

<span class="pln">
SELECT </span><span class="pun">*</span><span class="pln"> INTO </span><span class="typ">EmployeeBackup1</span><span class="pln"> IN </span><span class="str">'Backup.mdb'</span><span class="pln">
FROM </span><span class="typ">Customers</span><span class="pun">;</span><span class="pln">
</span>

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

<span class="pln">
SELECT </span><span class="typ">EmployeeName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ContactName</span><span class="pln"> INTO </span><span class="typ">EmployeeBackup1</span><span class="pln">
FROM </span><span class="typ">Employee</span><span class="pun">;</span><span class="pln">
</span>