Summary of the "SQL SELECT INTO Statement" page on W3Schools:

• Purpose: The SQL SELECT INTO statement is used to copy data from one table into a new table.

• Syntax:

  • To copy all columns:

  SELECT * INTO newtable FROM oldtable WHERE condition;
  

  • To copy specific columns:

  SELECT column1, column2 INTO newtable FROM oldtable WHERE condition;
  

• The new table is automatically created with the same column names and data types as the old table. You can rename columns using the AS clause.

• Examples:

  • Create a backup copy of a table.
  • Copy only selected columns.
  • Copy specific rows using a WHERE clause (e.g., only German customers).
  • Copy data from multiple tables by using a JOIN.
  • Copy a table to another database using the IN clause.

• Tip: You can create a new, empty table with the same schema by selecting with a condition that always evaluates to false (e.g., WHERE 1 = 0).

• Quiz: The page includes a quiz reinforcing that the primary purpose of SELECT INTO is to "copy data from one table into a new table".[1]

1

SQL SELECT INTO Statement

The SQL SELECT INTO Statement

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

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 Customers:

SELECT * INTO CustomersBackup2017  
FROM Customers;

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

SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'  
FROM Customers;

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

SELECT CustomerName, ContactName INTO CustomersBackup2017  
FROM Customers;

The following SQL statement copies only the German customers into a new table:

SELECT * INTO CustomersGermany  
FROM Customers  
WHERE Country = 'Germany';

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

SELECT Customers.CustomerName, Orders.OrderID  
INTO CustomersOrderBackup2017  
FROM Customers  
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

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;