Create Table as Select .... [CORE796]

[firebird-automations]

Submitted by: @pcisar

Votes: 30

SFID: 807931#⁠
Submitted By: pcisar

Submitted by mailto:nick@upsonuk.com

I'd love to see "create table as select ...." as
Ingres does.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Commented by: lacak (lacak)

SQL 2003:
--------------
CREATE [ <table scope> ] TABLE <table name> [ (<column name list>) ]
AS <subquery>
WITH NO DATA | WITH DATA

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 10820 ] => Firebird [ 15201 ]

[firebird-automations]

Commented by: Umberto Masotti (umasotti)

Would be nice to have also

CREATE [TEMPORARY] TABLE <table name> [(<column name list>)]
AS <subquery>
WITH [NO] DATA [ON COMMIT <DELETE | PRESERVE> ROWS]

The version WITH DATA means a starting point to be materialized on first access (why elaborate it if don't used?) and discarded at commit or disconnection. In some way resemble the ClientDataSet clone() command.

In this case <subquery> can have filtering parameters? If first access is forced to be a SELECT ... FROM TEMP_TABLE_WITH_DATA ( <params list> ) like selectable stored procedures, this will create a subset to work with. From the engine POV maybe there is no difference, but developer hasn't to filter every access.
Don't know if standard SQL has better choice for syntax, but instead of SELECT I'd prefer something like MATERIALIZE TEMP_TABLE_WITH_DATA ( <parameter list>) before accessing the table in transaction and/or session.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ] =>

[firebird-automations]

Commented by: Lukas Eder (lukas.eder)

I think it is worth mentioning that among the 16 RDBMS currently supported by jOOQ, 15 support this statement or an equivalent one:

SELECT ... INTO table FROM ... is supported by:

- HSQLDB
- MS Access
- PostgreSQL
- SQL Server
- Sybase ASE
- Sybase SQL Anywhere

CREATE TABLE AS SELECT ... is supported by:

- CUBRID
- DB2
- Derby
- H2
- Ingres
- MariaDB
- MySQL
- Oracle
- PostgreSQL
- SQLite