Create a table on a specified datasource. If the datasource is not specified, Cirro identifies the most efficient datasource to create the table.

Syntax

CREATE {temp|temporary} TABLE tablename
(columnname datatype [ NULL | NOT NULL ] [ ,...n ])
[WITH NATIVE (nativecommands)];

Arguments

  • temp | temporary - Create a temporary table for the current session that is dropped when session ends.

  • tablename - The fully qualified or single part name for the table.

  • columnname - Name for the column.

  • datatype - Use a Cirro data type or one native to the destination datasource. Enclose destination data types in single quotation marks.

  • nativecommands - Native commands that will be understood by the destination database. For example, Oracle CREATE TABLE

Examples

Create a Cirro table in a postgres database.

create table dataxchange.postgres.public.doctest
(id INTEGER, name CHAR(10), surname CHAR(10), email VARCHAR (50));

Create an Oracle table with native data type ‘NUMBER(20)’ for column2, and native table extension command ‘PARALLEL’

CREATE TABLE ORACLE1.ORACLE.CIRRO.sample_permanent (
    column1 INT,
    column2 'NUMBER(20)'
)
WITH NATIVE 'PARALLEL';

Create a temporary Oracle table with native data type ‘NUMBER(20)’ for column2, and native table extension command ‘PARALLEL’

CREATE TEMP TABLE ORACLE1.ORACLE.CIRRO.sample_temporary (
    column1 INT,
    column2 'NUMBER(20)'
)
WITH NATIVE 'PARALLEL';

Create a table at a location of the optimizer’s choosing

CREATE TABLE sample_permanent2_anywhere (column1 INT); 

Create a temporary table at a location of the optimizer’s choosing.

CREATE TEMP TABLE sample_temporary2_anywhere (column1 INT);

See Also