Create Table in Teradata

In this tutorial, you will learn how to create a table in the Teradata database with examples.

---

Table is a basic element in any RDBMS (Relational Database Management System). Table creation in Teradata is almost similar to the other relational databases. Teradata tables are created using CREATE TABLE or CT command.

Teradata CREATE TABLE Syntax

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

In this syntax,

SET/ MULTISET – defines the uniqueness of the records in the table. The SET table does not allow any duplicate record wherein the MULTISET table allows duplicate tables. If you do not specify anything Teradata by default creates SET.

FALLBACK – defines duplicate copy of the table will be maintained in case of any failure. 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(1..N): Name of the column you want in the table.
• data_type: Specifies 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 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.

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

Teradata CREATE TABLE Example

The following example creates a set table named Employee in Company_Database with the FALLBACK option.

CREATE SET TABLE Company_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 table in Teradata:

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

Teradata create table as select:

The below example shows to create an employee table base 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 Company_Database.single_employee AS (
   SELECT
      *
   FROM Company_Database.Employee e
   WHERE
      e.Marital_Status = 'S'
   )
WITH DATA;

Teradata create table from another table with full data:

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

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