CREATE TABLE

The Teradata CREATE TABLE (short key CT) statements are used to create new tables.

Example:

Input: CREATE TABLE

1
2
3

CT tab1 (
     id INT
);

Output

1
2
3
4
5
6

CREATE
     TABLE
          tab1 (
               id INTEGER
          )
;

When CREATE tab2 AS tab1 is executed, the structure copied from tab1 is used to create table tab2. If the CREATE TABLE statement includes WITH DATA operator, then the data from tab1 is also copied into tab2. When CREATE AS is used, the CONSTRAINT row in the source table is retained in the new table.

• If •session_mode = Teradata, the default table type is SET in which duplicate records must be removed. This is done by adding the MINUS operator in the migrated scripts.
• If •session_mode = ANSI, the default table type is MULTISET in which duplicate records must be allowed.

If the source table has a PRIMARY KEY or a UNIQUE CONSTRAINT, then it will not contain any duplicate records. In this case, the MINUS operator is not required or added to remove duplicate records.

Example:

Input: CREATE TABLE AS with DATA (session_mode=Teradata)

1
2

CREATE TABLE tab2 
    AS tab1 WITH DATA;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

BEGIN 
    CREATE TABLE tab2 (
            LIKE tab1 INCLUDING ALL EXCLUDING PARTITION EXCLUDING RELOPTIONS
                      );

    INSERT INTO tab2
	SELECT * FROM tab1
            MINUS SELECT * FROM tab2;
END
;
/

Example: Input: CREATE TABLE AS with DATA AND STATISTICS

1
2
3
4

CREATE SET VOLATILE TABLE tab2025
 AS ( SELECT * from tab2023 )
 WITH DATA AND STATISTICS 
 PRIMARY INDEX (LOGTYPE, OPERSEQ);

Output

1
2
3
4
5

CREATE LOCAL TEMPORARY TABLE tab2025 
 DISTRIBUTE BY HASH ( LOGTYPE, OPERSEQ ) 
 AS ( SELECT * FROM tab2023 );

 ANALYZE tab2025;