Teradata create table syntax with examples
We can create SET/MULTISET tables in Teradata. There are few differences between SET and MULTISET tables.Let’s discuss about syntax to create these tables.
Create Table Syntax:
We have to specify the table type,table options,column definitions and Index definitions in the create table statement of Teradata.
|
1 2 3 4 |
CREATE SET/MULTISET TABLE DB_NAME.TABLE_NAME, <Table Options> (Column1 datatype,Column2 datatype,....Column-N datatype) <Index Definitions>; |
Create SET table example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE SET TABLE BANKING_DB.CUSTOMER ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Cust_Id INTEGER, Cust_Name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Join_Date DATE FORMAT 'YYYY/MM/DD', Mobile_Number INTEGER, Login_Count DECIMAL(18,0) ) UNIQUE PRIMARY INDEX ( Cust_Id ); |
The Teradata SET table did not allow duplicate values in table.In SET table,we need to specify the Unique primary index in the index definition area which helps teradata to avoid the duplicate values.
Create MULTISET table example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE MULTISET TABLE Banking_DB.Cust_Transaction ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Cust_Id INTEGER, Transaction_no VARCHAR(20), Transaction_Date DATE FORMAT 'YYYY/MM/DD', Amount INTEGER, Balance DECIMAL(18,0) ) PRIMARY INDEX ( Cust_Id ); |
MULTISET tables allow duplicate values in the table.We can create the MULTISET table without specifying the Index definitions such Primary Index or Unique Primary Index.
By default,it allow the duplicate values if we not specifying index definitions.
Recommended Articles
- Insert Into Statement in Teradata
- INSERT INTO SELECT Statement in Teradata with examples
- Grant Select access to table in Teradata
