SELECT INTODML
Buy Me A Coffee

A shorthand for creating a new table from the result of a SELECT statement.

Creates a new table from a SELECT result, optionally with data. The fastest way to materialize a complex query. Consider using a materialized view instead if the query should be refreshable.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0
PostgreSQL ✓ Supported — SELECT ... INTO new_table is supported (with PL/pgSQL caveats). CREATE TABLE ... AS SELECT is the standard alternative.
SQL Server ✓ Supported 2016
Oracle ✗ Not Supported — Oracle does not support the SQL-Server style SELECT ... INTO new_table shorthand — use CREATE TABLE ... AS SELECT ... or INSERT INTO ... SELECT ... instead.
SQLite ✓ Supported 3.6.5

Details

This syntax is a convenient way to duplicate the structure and data of an existing table into a new one.

Standard Syntax

SELECT column1, column2 INTO new_table FROM old_table;

Version Support

MySQL: Since 8.0 PostgreSQL: Since ? SQL Server: Since 2016 Oracle: Not supported SQLite: Since 3.6.5

Per-Database Syntax & Notes

MySQL

CREATE TABLE new_table AS SELECT column1, column2 FROM old_table;

SQL Server

SELECT column1, column2 INTO new_table FROM old_table;

SQLite

CREATE TABLE new_table AS SELECT column1, column2 FROM old_table;