Issue Details (XML | Word | Printable)

Key: CORE-796
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Pavel Cisar
Votes: 30
Watchers: 12
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Create Table as Select ....

Created: 17/Sep/03 12:00 AM   Updated: 22/Jul/14 11:06 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

SF_ID: 807931


 Description  « Hide
SFID: 807931#
Submitted By: pcisar

Submitted by nick@upsonuk.com

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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
lacak added a comment - 12/Jan/07 06:28 AM
SQL 2003:
--------------
CREATE [ <table scope> ] TABLE <table name> [ (<column name list>) ]
 AS <subquery>
WITH NO DATA | WITH DATA

Umberto Masotti added a comment - 13/Apr/09 12:44 PM - edited
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.

Lukas Eder added a comment - 22/Jul/14 11:06 AM
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