Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Create Table as Select .... [CORE796] #1181

Open
firebird-automations opened this issue Sep 17, 2003 · 6 comments
Open

Create Table as Select .... [CORE796] #1181

firebird-automations opened this issue Sep 17, 2003 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: lacak (lacak)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ] =>

@firebird-automations
Copy link
Collaborator Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant