CREATE TABLE from another server query
hi 😊,
i'm able to LOAD DATA in IRIS from a rather complex, say Oracle's, query. It works pretty well but requires a target table created aforehand:
LOAD DATA FROM JDBC CONNECTION SOME_OTHER_SERVER QUERY 'complex query here' INTO TargetTableis there a way to base such target table on the same query ?
CREATE FOREIGN TABLE seems to require a column definition which I would prefer to be taken from the query
CREATE TABLE AS SELECT seems to be for local queries and other methods, like Linked Table Wizard or %SYSTEM.SQL.Schema.ImportDDL(), rather for Tables/Views
I'd think is doable since there is a mapping to java datatypes in between ? Moreover CREATE FOREIGN TABLE documentation hints that:
Foreign tables created in this way copy column definitions from the external data source, including column names and data types (when supported). A foreign table can copy column definitions from multiple tables if the query specifies joined tables from the external data source.
thanks ! best regards,
Comments
To base a target table on a complex query without predefining its structure, you can use the CREATE FOREIGN TABLE command with the QUERY option. This enables the table to mirror the schema of the query results dynamically, as well as project the data directly from the query on an external server.
Here’s an example syntax for this approach:
CREATE FOREIGN TABLE [IF NOT EXISTS] table-name
[ ( column type, column2 type2, ... ) ]
SERVER server-name QUERY query [NOVALIDATE];
- In this command:
table-nameis the name of the foreign table you are creating.server-nameis the name of the defined foreign server.queryis the actual SQL query to retrieve data from the external source.
- The
QUERYoption allows you to specify the external SQL query and dynamically fetch its schema and data.
If the external server supports such functionality, column definitions (names and types) are copied directly from the query projection, removing the need to define them manually 123.
Sources: