Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 26 May 2009 @ 11:46:13 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Creating one table from another
 
From:   Singh, Lakhwant

Hi Mandasmitha,

Following is the first mail on this subject from "Anomy.Anom":

  The two methods for creating a new table from an existing one are:  


  1. Using CREATE TABLE statement  


  2. Using CREATE AS SELECT syntax (with data/ with no data) Out of the above two, which is more efficient? Is there any other method which is more efficient than this?  


Here Anomy.Anom was creating a new table from "existing table".

Your question is different.

The first method you mentioned is out of context as you are just creating table using a standard DDL statement and NOT from existing table.

2nd and 3rd methods in your question are pretty much same. Still, if you ask me, I would go for 3rd. As far as "passing on the constraints" is concerned, both these methods CANNOT pass on constraints from existing table to the new table.

Example:

     CREATE TABLE test1 (a INTEGER NOT NULL, b CHAR(4) NOT NULL)
     PRIMARY INDEX (a)
     UNIQUE INDEX (b);

     CREATE TABLE test2 as (SELECT a, b FROM test1) WITH [NO] DATA;

     SHOW TABLE test2;

You will get:

     CREATE SET TABLE test2, FALLBACK,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           a INTEGER,
           b CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX (a);

Note here that there is no "UNIQUE INDEX" and no "NOT NULL" defined in the new table CREATE statement. But both these constraints do exist in the existing table.


Thanks

Lakhwant



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023