varios doubts on table creation in teradata

General

varios doubts on table creation in teradata

Hi, Actually i am new to teradata. I you find any thing silly please ignore and clarify my doubt. Kindly let me know if more explanation is required.

New Tables:

1. CREATE Table Employee_Table12 AS

(SELECT * FROM Employee_Table8) with data 

2. CREATE Table Employee_Table13 AS

   Employee_Table8 with data 

3. CREATE Table Employee_Table14 AS

   Employee_Table8 with data 

   primary_index(dept_no)

4. CREATE Table Employee_Table15 AS

   (select * from Employee_Table8) with data 

   primary_index(dept_no)

Existing table:

CREATE SET Table Employee_Table8

(

Employee_No INTEGER

,Dept_No INTEGER

,Last_Name CHAR(20)

,First_Name VARCHAR(20)

,Salary DECIMAL(10,2)

,Hire_Date DATE

,Social_Security Char(11)

) Unique Primary Index (Employee_No)

Unique Index (Social_Security) ;

Questions;

1. what is the difference in creating the tables Employee_Table12 & Employee_Table12

2. If i want to change the index in the new table,  do i need to follow which syntax (either 3 or 4 )? or can i use the both?

1 REPLY
N/A

Re: varios doubts on table creation in teradata

CREATE TABLE AS existing table copies all index definitions and table/column options (like FORMAT, NOT NULL or SET/MULTISET), while CREATE TABLE AS SELECT drops all those resetting everything to defaults.

When you do a SHOW TABLE you can easily see the differences.

You can change index definitions using both techniques, test it and do SHOW TABLE again