Create Table in Teradata

Creating a table in Teradata is almost similar to other Relational Databases. CREATE TABLE or CT is the command used to create a table in Teradata.

CREATE TABLE Syntax:

CREATE SET/MULTISET TABLE  database_name.table_name, [NO] FALLBACK
   (column_name   data_type attribute,
    column_name   data_type attribute,
    column_name   data_type attribute)
[UNIQUE][NO] PRIMARY INDEX (primary_index_column);

SET/MULTISET syntax is used to define the uniqueness of the records in the table. The SET table does not allow any duplicate records wherein the MULTISET table allows duplicate records. If you do not specify anything Teradata by default creates a SET table.

FALLBACK defines duplicate copy of the table will be maintained in case of any failure. The default is the NO fallback. Define FALLBACK only on the critical tables in the database as it consumes extra space.

Database_Name: Where the table will be created.

Table Name: The name of the table.

Column_name: Name of the column you want in the table.

Data_type: Specifies a single datatype for a column.

Attribute: Defines attributes for a column. Example – NOT NULL, NOT CASE SPECIFIC, UPPER CASE, FORMAT, etc.

[UNIQUE/NO] PRIMARY INDEX: You have to define at least one primary index in Teradata that can be unique or non-unique in nature. If you don’t want any primary index in your table there is an option called NO PRIMARY INDEX in Teradata.

The primary index column defines the column(s) that will work as a primary index in Teradata. Column or combinations of columns can be unique or non-unique.

CREATE TABLE Teradata Example:

The following example creates a set table called Employee in Employee_database with the FALLBACK option.

CREATE SET TABLE Employee_Database.Employee, FALLBACK
   (Employee_Id     INTEGER,
    Employee_Name   CHAR(25),
    Salary           DECIMAL(8,2),
    DOB              DATE FORMAT 'YYYY-MM-DD',
    Job_Title        VARCHAR(25),
    Dept_No          SMALLINT,
    Marital_Status   CHAR,
    No_Of_Dependents BYTEINT)
UNIQUE PRIMARY INDEX (Employee_Id);

Once the table is created you can do the SHOW TABLE command to see the table definition in Teradata.

show table Employee_Database.Employee;
CREATE SET TABLE Employee_Database.Employee ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      Employee_Id INTEGER,
      Employee_Name CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      Salary DECIMAL(8,2),
      DOB DATE FORMAT 'YYYY-MM-DD',
      Job_Title VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      Dept_No SMALLINT,
      Marital_Status CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      No_Of_Dependents BYTEINT)
UNIQUE PRIMARY INDEX ( Employee_Id );

Other versions of creating tables in Teradata:

By now you have learned how to create a table in Teradata. There are other various methods by which you can get your desired table quickly.

Teradata create table as select:

The below example shows to create an employee table based on their marital status. Suppose you want only those employees whose marital status is SINGLE. This can be easily done using our existing table.

CREATE TABLE Employee_Database.bachelor_employee AS (
   SELECT
      *
   FROM Employee_Database.Employee e
   WHERE
      e.Marital_Status = 'S'
   )
WITH DATA;

Teradata create table from another table with full data:

The below example will just create another copy of the employee table with data.

CREATE TABLE Employee_Database.new_employee AS (
   SELECT
      *
   FROM Employee_Database.Employee
   )
WITH DATA;