How can I do a "SELECT INTO TABLE" in Teradata?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
KS
Enthusiast

How can I do a "SELECT INTO TABLE" in Teradata?

I know -In SQL SERVER - We can have a Select statement which gets us the columns / values from a tables and Inserts / “INTO” another table,I am looking for a similar attempt in Teradata..?

The Select statement also has CASE STATEMENTS and ALIASED COLUMNS while INSERT into 2nd table. Please suggest , if I should go for SP , any sample code..?


Accepted Solutions
Junior Contributor

Re: How can I do a "SELECT INTO TABLE" in Teradata?

At least you should clean it up when you cut&paste a question from StackOverflow :)

 

There's CREATE TABLE ... AS (SELECT ...) WITH DATA, but don't forget to add a Primary Index (otherwise it defaults to the 1st column) 

1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: How can I do a "SELECT INTO TABLE" in Teradata?

At least you should clean it up when you cut&paste a question from StackOverflow :)

 

There's CREATE TABLE ... AS (SELECT ...) WITH DATA, but don't forget to add a Primary Index (otherwise it defaults to the 1st column) 

KS
Enthusiast

Re: How can I do a "SELECT INTO TABLE" in Teradata?

Thanks dnoeth ,

Point noted I will take care of your suggestion , (I posted both the places as I see few great people there as well.... :)...)

One query, -> 

I have a requirement as below

Select Col11,Col2,Col3 INTO TABLE 2 FROM (SELCT (Col1 +2N) as Col11 ,case 1::1 as Col2 ,Col3 from TABLE1)

 

I understand creating two tables and then populating as per need can be an option as suggested , but abouve query line seems to use "select"

Please suggest.

-k

Teradata Employee

Re: How can I do a "SELECT INTO TABLE" in Teradata?

Suggest that the section on create table AS be reviewed per dnoeth's note...

CREATE TABLE2 AS (Select Col11,Col2,Col3 FROM (SELECT (Col1 +2N) as Col11 ,case 1::1 as Col2 ,Col3 from TABLE1)) WITH DATA PRIMARY INDEX (whichever columns should be your PI);

Note that the Teradata syntax is ANSI while SQL Server is not.

Re: How can I do a "SELECT INTO TABLE" in Teradata?

You can use this syntaxe:

 

INSERT INTO TABLE2

select col1, col2 ....

from TABLE1