INSERT SELECT Vs Create TAble WITH DATA - Which one is faster (TD 16.20)

General
Enthusiast

INSERT SELECT Vs Create TAble WITH DATA - Which one is faster (TD 16.20)

Hi,

 

Which one is faster -  below is 2 form for table creation with data. Which one is faster ?

 

  1.  Insert into a empty table : INSERT into T2 Select * from T1
  2. CREATE TABLE T2 AS T1 WITH DATA

We are using teradata 16.20.  

Thanks - Sandeep.
Tags (1)
2 REPLIES 2
Highlighted
Teradata Employee

Re: INSERT SELECT Vs Create TAble WITH DATA - Which one is faster (TD 16.20)

Assuming the structure of T2 matches T1 there should be no real difference as far as actually inserting the data..

But clearly CREATE TABLE AS will have some extra overhead to create the table, so you should expect that to take just a bit longer.

Junior Contributor

Re: INSERT SELECT Vs Create TAble WITH DATA - Which one is faster (TD 16.20)

And if someone claims that CREATE * INSERT is much better than CREATE TABLE AS it's usually because the latter was a CREATE TABLE AS (SELECT ...) without defining the PI, which results in a (maybe bad) PI on the 1st column (and maybe a change from MULTISET to SET).