sql_select_into

SQL SELECT INTO and CREATE TABLE AS SELECT

SQL provides mechanisms to copy data from one or more tables into a new table. The specific syntax can vary between database systems.

The SELECT INTO statement is primarily used in SQL Server and MS Access, while the CREATE TABLE AS SELECT (CTAS) statement is the standard SQL way and is used in systems like PostgreSQL, MySQL, and Oracle.

The SQL SELECT INTO Statement (SQL Server / MS Access)

In SQL Server and MS Access, the SELECT INTO statement copies data from one table and inserts it into a new table. The new table is created automatically by the statement.

SQL SELECT INTO Syntax:

To copy all columns into the new table:

SELECT *
INTO NewTable
FROM OldTable
WHERE condition;

To copy specific columns into the new table:

SELECT column1, column2, column_name(s)
INTO NewTable
FROM OldTable
WHERE condition;

The new table will be created with the column names and data types as defined in the SELECT statement. You can assign new names to columns using the AS clause.

Note: The IN externaldb clause, to copy into a table in another database, is specific to some systems like MS Access or SQL Server (for linked servers). Its usage and syntax can vary.

The SQL CREATE TABLE AS SELECT (CTAS) Statement (Standard SQL, PostgreSQL, MySQL, Oracle)

The more standard and widely supported way to create a table from a query result is the CREATE TABLE AS SELECT (CTAS) statement.

SQL CTAS Syntax:

To copy all columns into the new table:

CREATE TABLE NewTable AS
SELECT *
FROM OldTable
WHERE condition;

To copy specific columns into the new table:

CREATE TABLE NewTable AS
SELECT column1, column2, column_name(s)
FROM OldTable
WHERE condition;

Similar to SELECT INTO, the new table’s structure is derived from the result set of the SELECT query.

SQL SELECT INTO / CTAS Examples

Assume we have a Customers table. We want to create backups or derived tables.

1. Create a backup copy of Customers:

SQL Server / MS Access:

SELECT *
INTO CustomersBackup2024
FROM Customers;

Standard SQL (PostgreSQL, MySQL, Oracle):

CREATE TABLE CustomersBackup2024 AS
SELECT *
FROM Customers;

2. Copy only a few columns into the new table:

SQL Server / MS Access:

SELECT CustomerName, ContactName
INTO CustomerContactsBackup
FROM Customers;

Standard SQL (PostgreSQL, MySQL, Oracle):

CREATE TABLE CustomerContactsBackup AS
SELECT CustomerName, ContactName
FROM Customers;

3. Copy only customers from a specific country (e.g., ‘Germany’) into the new table:

SQL Server / MS Access:

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

Standard SQL (PostgreSQL, MySQL, Oracle):

CREATE TABLE GermanCustomers AS
SELECT *
FROM Customers
WHERE Country = 'Germany';

4. Copy data from more than one table into the new table (e.g., Customers and Orders):

SQL Server / MS Access:

SELECT c.CustomerName, o.OrderID, o.OrderDate
INTO CustomerOrderSummary
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

Standard SQL (PostgreSQL, MySQL, Oracle):

CREATE TABLE CustomerOrderSummary AS
SELECT c.CustomerName, o.OrderID, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

Tip: Creating an empty table with the schema of another.

You can create a new, empty table with the same structure as an existing table by adding a WHERE clause that returns no data (e.g., WHERE 1=0).

SQL Server / MS Access:

SELECT *
INTO NewEmptyCustomersTable
FROM Customers
WHERE 1=0; -- This condition is always false

Standard SQL (PostgreSQL, MySQL, Oracle):

CREATE TABLE NewEmptyCustomersTable AS
SELECT *
FROM Customers
WHERE 1=0; -- This condition is always false

Some systems also provide a CREATE TABLE ... LIKE ... syntax (e.g., MySQL, PostgreSQL) for copying table structure without data, which can be more direct: CREATE TABLE NewEmptyCustomersTable LIKE Customers;