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
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Supported via workaround | - | Workaround available in all listed versions. No native SQL Server-style SELECT ... INTO new_table shorthand. Use CREATE TABLE ... AS SELECT instead. |
| PostgreSQL | Native | - | SELECT ... INTO new_table is supported (with PL/pgSQL caveats). CREATE TABLE ... AS SELECT is the standard alternative. |
| SQL Server | Native | 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 via workaround | - | Workaround since 3.6.5. No native SQL Server-style SELECT ... INTO new_table shorthand. Use CREATE TABLE ... AS SELECT instead. |
Details
This syntax is a convenient way to duplicate the structure and data of an existing table into a new one.
Standard Syntax
Version Support
Per-Database Syntax & Notes
MySQL Workaround
Use CREATE TABLE ... AS SELECT to create a new table from a query result. Available in all listed versions. No native SQL Server-style SELECT ... INTO new_table shorthand. Use CREATE TABLE ... AS SELECT instead.
SQL Server Native syntax
SQLite Workaround
Use CREATE TABLE ... AS SELECT to create a new table from a query result. Since: 3.6.5. No native SQL Server-style SELECT ... INTO new_table shorthand. Use CREATE TABLE ... AS SELECT instead.