Creating a table in Teradata is almost similar to other Relational Databases. CREATE TABLE or CT is the command is used to create table in Teradata.
CREATE TABLE Teradata 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 PRIMARY INDEX (primary_index_column);
SET/ MULTISET syntax is used to define the uniqueness of the records in the table. SET table do not allow any duplicate record wherein MULTISET table allow duplicate table. 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: 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 atleast 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 option call 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.
CREATE TABLE Teradata Example:
The following example create a set table called Employee in Employee_database with 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 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 create table in Teradata:
By now you have learnt how to create table in teradata. There are other various methods by which you can get your desire table quickly.
Teradata create table as select:
Below example shows to create a employee table base on there marital status. Suppose you want only those employee 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 employee table with data.
CREATE TABLE Employee_Database.new_employee AS ( SELECT * FROM Employee_Database.Employee ) WITH DATA;