SnowConvert: Sybase IQ DDLs¶
Translation reference for all the DDL statements that are preceded by the CREATE
word.
CREATE TABLE¶
Description¶
Creates a new table in the current database. You define a list of columns, which each hold data of a distinct type. The owner of the table is the issuer of the CREATE TABLE command.
For more information, please refer to CREATE TABLE
documentation.
Grammar Syntax¶
CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE
[ IF NOT EXISTS ] [ <owner>. ]<table-name>
… ( <column-definition> [ <column-constraint> ] …
[ , <column-definition> [ <column-constraint> ] …]
[ , <table-constraint> ] … )
|{ ENABLE | DISABLE } RLV STORE
…[ IN <dbspace-name> ]
…[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ AT <location-string> ]
[PARTITION BY
<range-partitioning-scheme>
| <hash-partitioning-scheme>
| <composite-partitioning-scheme> ]
<column-definition> ::=
<column-name> <data-type>
[ [ NOT ] NULL ]
[ DEFAULT <default-value> | IDENTITY ]
[ PARTITION | SUBPARTITION ( <partition-name> IN <dbspace-name> [ , ... ] ) ]
<default-value> ::=
<special-value>
| <string>
| <global variable>
| [ - ] <number>
| ( <constant-expression> )
| <built-in-function>( <constant-expression> )
| AUTOINCREMENT
| CURRENT DATABASE
| CURRENT REMOTE USER
| NULL
| TIMESTAMP
| LAST USER
<special-value> ::=
CURRENT
{ DATE | TIME | TIMESTAMP | USER | PUBLISHER }
| USER
<column-constraint> ::=
IQ UNIQUE ( <integer> )
| { [ CONSTRAINT <constraint-name> ]
{ UNIQUE
| PRIMARY KEY
| REFERENCES <table-name> [ ( <column-name> ) ] [ ON { UPDATE | DELETE } RESTRICT ] }
[ IN <dbspace-name> ]
| CHECK ( <condition> )
}
<table-constraint> ::=
[ CONSTRAINT <constraint-name> ]
{ { UNIQUE | PRIMARY KEY } ( <column-name> [ , … ] )
[ IN <dbspace-name> ]
| <foreign-key-constraint>
| CHECK ( <condition> )
}
<foreign-key-constraint> ::=
FOREIGN KEY [ <role-name> ] [ ( <column-name> [ , <column-name> ] … ) ]
…REFERENCES <table-name> [ ( <column-name> [ , <column-name> ] … ) ]
…[ <actions> ] [ IN <dbspace-name> ]
<actions> ::=
[ ON { UPDATE | DELETE } RESTRICT ]
<location-string> ::=
{ <remote-server-name>. [ <db-name> ].[ <owner> ].<object-name>
| <remote-server-name>; [ <db-name> ]; [ <owner> ];<object-name> }
<range-partitioning-scheme> ::=
RANGE ( <partition-key> ) ( <range-partition-decl> [,<range-partition-decl> … ] )
<partition-key> ::= <column-name>
<range-partition-declaration> ::=
<range-partition-name> VALUES <= ( {<constant> | MAX } ) [ IN <dbspace-name> ]
<hash-partitioning-scheme> ::=
HASH ( <partition-key> [ , <partition-key>, … ] )
<composite-partitioning-scheme> ::=
<hash-partitioning-scheme> SUBPARTITION BY <range-partitioning-scheme>
AT LOCATION¶
Description¶
Creates a remote table (proxy). (Sybase SQL Language Reference)
This syntax is not supported in Snowflake.
Grammar Syntax
AT <location-string>
Sample Source Patterns
Input Code:
CREATE TABLE t1
(
DATA VARCHAR(10)
)
AT 'SERVER_A.db1.joe.t1';
Output Code:
CREATE OR REPLACE TABLE t1
(
DATA VARCHAR(10)
)
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0002 - UNSUPPORTED REMOTE TABLE SYNTAX ***/!!!
AT 'SERVER_A.db1.joe.t1'
Related EWIs
SSC-EWI-SY0002: UNSUPPORTED REMOTE TABLE SYNTAX.
CONSTRAINTS
Description
This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. (Sybase SQL Language Reference)
Warning
This syntax is partially supported.
Grammar Syntax¶
<table-constraint> ::=
[ CONSTRAINT <constraint-name> ]
{ { UNIQUE | PRIMARY KEY } ( <column-name> [ , … ] )
[ IN <dbspace-name> ]
| <foreign-key-constraint>
| CHECK ( <condition> )
}
<foreign-key-constraint> ::=
FOREIGN KEY [ <role-name> ] [ ( <column-name> [ , <column-name> ] … ) ]
…REFERENCES <table-name> [ ( <column-name> [ , <column-name> ] … ) ]
…[ <actions> ] [ IN <dbspace-name> ]
<actions> ::=
[ ON { UPDATE | DELETE } RESTRICT ]
Sample Source Patterns¶
Input Code:¶
CREATE TABLE t_constraint (
id1 INT NOT NULL,
id2 INT PRIMARY KEY,
age INT CHECK (age >= 18),
email VARCHAR(255) UNIQUE,
product_id INT REFERENCES products(id) ON DELETE RESTRICT IN SOMEPLACE,
cod_iq VARCHAR(20) IQ UNIQUE(5),
CONSTRAINT unq_name_email UNIQUE (name, email),
CONSTRAINT fk_ord_line FOREIGN KEY (ord_id, line_id) REFERENCES ord_lines(ord_id,line_id)
);
Output Code:¶
CREATE OR REPLACE TABLE t_constraint (
id1 INT NOT NULL,
id2 INT PRIMARY KEY,
age INT
!!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
CHECK (age >= 18),
email VARCHAR(255) UNIQUE,
product_id INT REFERENCES products (id) ON DELETE RESTRICT ,
cod_iq VARCHAR(20)
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0003 - UNSUPPORTED IQ UNIQUE CONSTRAINT ***/!!!
IQ UNIQUE(5),
CONSTRAINT unq_name_email UNIQUE (name, email),
CONSTRAINT fk_ord_line FOREIGN KEY (ord_id, line_id) REFERENCES ord_lines (ord_id, line_id)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "sybase", "convertedOn": "03/19/2025", "domain": "test" }}'
;
Related EWIs¶
SSC-EWI-0035: CHECK STATEMENT NOT SUPPORTED.
SSC-EWI-SY0003: UNSUPPORTED IQ UNIQUE CONSTRAINT.
DEFAULT¶
Description¶
Defines the default value of a column in a create table.
Warning
This syntax is partially supported in Snowflake.
Grammar Syntax¶
<default-value> ::=
<special-value>
| <string>
| <global variable>
| [ - ] <number>
| ( <constant-expression> )
| <built-in-function>( <constant-expression> )
| AUTOINCREMENT
| CURRENT DATABASE
| CURRENT REMOTE USER
| NULL
| TIMESTAMP
| LAST USER
<special-value> ::=
CURRENT
{ DATE | TIME | TIMESTAMP | USER | PUBLISHER }
| USER
Sample Source Patterns¶
Input Code:¶
create table t_defaults
(
col1 timestamp default current utc timestamp,
col2 timestamp default current timestamp,
col3 varchar default current user,
col4 varchar default current remote user,
col5 varchar default last user,
col6 varchar default current publisher,
col7 varchar default current date,
col8 varchar default current database,
col9 varchar default current time,
col10 varchar default user,
col11 int default autoincrement,
col12 int identity,
col13 int default -10,
col14 int default 'literal',
col15 int default null
)
;
Output Code:¶
CREATE OR REPLACE TABLE t_defaults
(
col1 timestamp default CURRENT_TIMESTAMP,
col2 timestamp default CURRENT_TIMESTAMP,
col3 VARCHAR default CURRENT_USER,
col4 VARCHAR default
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0001 - UNSUPPORTED DEFAULT VALUE CURRENT REMOTE USER IN SNOWFLAKE ***/!!! current remote user,
col5 VARCHAR default
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0001 - UNSUPPORTED DEFAULT VALUE LAST USER IN SNOWFLAKE ***/!!! last user,
col6 VARCHAR default
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0001 - UNSUPPORTED DEFAULT VALUE CURRENT PUBLISHER IN SNOWFLAKE ***/!!! current publisher,
col7 VARCHAR default CURRENT_DATE,
col8 VARCHAR default CURRENT_DATABASE,
col9 VARCHAR default CURRENT_TIME,
col10 VARCHAR DEFAULT CURRENT_USER,
col11 INT IDENTITY ORDER,
col12 INT IDENTITY ORDER,
col13 INT default -10,
col14 INT default 'literal',
col15 INT default null
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "sybase", "convertedOn": "03/19/2025", "domain": "test" }}'
;
(ENABLE | DISABLE) RLV STORE¶
Description¶
Controls Row-Level Versioning Store functionality. (Sybase SQL Language Reference).
Note
This syntax is not needed in Snowflake.
Grammar Syntax¶
{ ENABLE | DISABLE } RLV STORE
Sample Source Patterns¶
Input Code:¶
CREATE TABLE rlv_table
(id INT)
ENABLE RLV STORE;
Output Code:¶
CREATE TABLE rlv_table
(id INT)
;
IF NOT EXISTS¶
Description¶
Ensures the table is created only if it does not already exist, preventing duplication and errors in your SQL script. (Sybase SQL Language Reference).
This syntax is fully supported in Snowflake.
Grammar Syntax
IF NOT EXISTS
Sample Source Patterns
Input Code:
CREATE TABLE IF NOT EXISTS table1 (
col1 INTEGER
);
Output Code:
CREATE TABLE IF NOT EXISTS table1 (
col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "sybase", "convertedOn": "03/19/2024" }}';
IN DBSPACE
Description
Specifies the DB space for data storage. (Sybase SQL Language Reference).
Note
This syntax is not needed in Snowflake. Snowflake automatically handles storage.
Grammar Syntax¶
IN <dbspace-name>
Sample Source Patterns¶
Input Code:¶
CREATE TABLE dbspace_table (
id INT PRIMARY KEY
)
IN my_dbspace;
Output Code:¶
CREATE OR REPLACE TABLE dbspace_table (
id INT PRIMARY KEY
);
ON COMMIT¶
Description¶
Specifies the behaviour of the temporary table when a commit is done. (Sybase SQL Language Reference)
Warning
This syntax is partially supported.
Grammar Syntax¶
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
Sample Source Patterns¶
Input Code:¶
CREATE LOCAL TEMPORARY TABLE temp_employees (
DATA VARCHAR(255)
) ON COMMIT DELETE ROWS;
CREATE LOCAL TEMPORARY TABLE temp_projects (
DATA VARCHAR(255)
) ON COMMIT PRESERVE ROWS;
Output Code:¶
CREATE OR REPLACE TEMPORARY TABLE temp_employees (
DATA VARCHAR(255)
)
-- --** SSC-FDM-0008 - ON COMMIT NOT SUPPORTED **
-- ON COMMIT DELETE ROWS
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "sybase", "convertedOn": "03/19/2025", "domain": "test" }}'
;
CREATE OR REPLACE TEMPORARY TABLE temp_projects (
DATA VARCHAR(255)
) ON COMMIT PRESERVE ROWS
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "sybase", "convertedOn": "03/19/2025", "domain": "test" }}'
;
Related EWIs¶
SSC-FDM-0008: On Commit not supported.
PARTITION BY¶
Description¶
All rows of a table partition are physically colocated. (Sybase SQL Language Reference)
Note
This syntax is not needed in Snowflake.
Grammar Syntax¶
PARTITION BY
<range-partitioning-scheme>
| <hash-partitioning-scheme>
| <composite-partitioning-scheme>
<range-partitioning-scheme> ::=
RANGE ( <partition-key> ) ( <range-partition-decl> [,<range-partition-decl> … ] )
<partition-key> ::= <column-name>
<range-partition-declaration> ::=
<range-partition-name> VALUES <= ( {<constant> | MAX } ) [ IN <dbspace-name> ]
<hash-partitioning-scheme> ::=
HASH ( <partition-key> [ , <partition-key>, … ] )
<composite-partitioning-scheme> ::=
<hash-partitioning-scheme> SUBPARTITION BY <range-partitioning-scheme>
Sample Source Patterns¶
Input Code:¶
-- Range Partitioning
CREATE TABLE sales (
sale_id INT,
sale_date DATE,
amount DECIMAL(10, 2)
)
PARTITION BY RANGE (sale_date) (
PARTITION p1 VALUES <= ('2023-01-01'),
PARTITION p2 VALUES <= ('2024-01-01'),
PARTITION p3 VALUES < MAXVALUE
);
-- Hash Partitioning
CREATE TABLE customers (
customer_id INT,
customer_name VARCHAR(255)
)
PARTITION BY HASH (customer_id) (
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);
-- Composite Partitioning (Hash-Range)
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY HASH (customer_id)
SUBPARTITION BY RANGE (order_date) (
PARTITION p1 VALUES <= ('2023-01-01'),
PARTITION p2 VALUES <= ('2024-01-01'),
PARTITION p3 VALUES < MAXVALUE
);
Output Code:¶
-- Range Partitioning
CREATE TABLE sales (
sale_id INT,
sale_date DATE,
amount DECIMAL(10, 2)
)
;
-- Hash Partitioning
CREATE TABLE customers (
customer_id INT,
customer_name VARCHAR(255)
);
-- Composite Partitioning (Hash-Range)
CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
amount DECIMAL(10,2)
);
TEMPORARY TABLES¶
Description¶
In Sybase IQ GLOBAL | LOCAL TEMPORARY
is used to create temporary tables that exist only for the session. These tables are session-specific and automatically deleted when the session ends. They help store intermediate results or work data without affecting the permanent database schema. It also can be created only by adding an #
at the beginning of the name.
Warning
This syntax is partially supported in Snowflake.
Grammar Syntax¶
CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE
Sample Source Patterns¶
Input Code:¶
CREATE LOCAL TEMPORARY TABLE TABLE01 (
col1 INTEGER
);
CREATE GLOBAL TEMPORARY TABLE TABLE02 (
col1 INTEGER
);
CREATE TABLE #TABLE03(
col1 INTEGER
);
Output Code:¶
CREATE OR REPLACE TEMPORARY TABLE TABLE01 (
col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "sybase", "convertedOn": "03/19/2025", "domain": "test" }}'
;
--** SSC-FDM-0009 - GLOBAL TEMPORARY TABLE FUNCTIONALITY NOT SUPPORTED. **
CREATE OR REPLACE TABLE TABLE02 (
col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "sybase", "convertedOn": "03/19/2025", "domain": "test" }}'
;
CREATE OR REPLACE TEMPORARY TABLE T_TABLE03 (
col1 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "sybase", "convertedOn": "03/19/2025", "domain": "test" }}'
;
Related EWIs¶
SSC-FDM-0009: GLOBAL TEMPORARY TABLE functionality not supported.