CREATE TABLE AS COLUMNS DEFINITIONS....

Database
c19
Enthusiast

CREATE TABLE AS COLUMNS DEFINITIONS....

Hi,

When a table is created using CREATE TABLE AS how does Teradata work out the column definitions.

For example, on the original table COMPLAINT_SMRY the COMPLAINT_REFERENCE_NUMBER is defined as CHAR(18) (I didn't design this by the way!)

So if I do something like this....

CREATE GLOBAL TEMPORARY TABLE TEMP
AS(
SELECT COMPLAINT_REFERENCE_NUMBER
FROM COMPLAINT_SMRY
) WITH NO DATA
PRIMARY INDEX (COMPLAINT_REFERENCE_NUMBER)

If I do a SHOW TABLE of TEMP the COMPLAINT_REFERENCE_NUMBER is defined as CHAR(18) (exactly as the original)

In my TEMP table I have a lot more columns which are exactly the same too.

Does Teradata look up the definitions? or does it take the biggest value from the column?

Thanks

3 REPLIES
Enthusiast

Re: CREATE TABLE AS COLUMNS DEFINITIONS....

c19,

As far as I know, Teradata would have as base to create the new table the source information, as it was defined originally.
So, if the column "COMPLAINT_REFERENCE_NUMBER" was originally created as CHAR(5), your new table (TEMP) should be created with a column having the same data type (CHAR(5)), even if you do not mention a data type during the table creation.

I just would like to call your attention to one point: you may create a table using "CREATE NEW_TABLE AS (SELECT ... )" or "CREATE NEW_TABLE AS OLD_TABLE" statements. The difference between both regards in the Primary Index definition, where the first statement would create the primary index using the first column in the source table and the second statement would create the NEW_TABLE with the same definition of OLD_TABLE, including the Primary Index.

I hope this helps!
c19
Enthusiast

Re: CREATE TABLE AS COLUMNS DEFINITIONS....

Fabio,

Thanks for the reply, I generally don't create my tables using CREATE TABLE AS, as I consider it bad practice (thats just my thoughts!)

I suppose my question was asked because its part of a bigger picture I am trying to create.

I'd like to create generated SQL which derives a table definition as the output.

so for example,

TABLE A
(
X INTEGER,
Y VARCHAR(10),
X DATE
)

but I would like to create a table with only X and Y, and my goal would be to create some generated sql
that could look at table A's column definitions for X and Y and produce the output of a table DDL

So have my generated sql

GENERATED SQL....

which will produce an output of,

(obviously this would be in correct teradata sql)

TABLE A
(
X INTEGER,
Y VARCHAR(10)
)

so this would also work if I only wanted to generate one column or six columns etc....

Thanks
Enthusiast

Re: CREATE TABLE AS COLUMNS DEFINITIONS....

c19,

I you are planning to create a partial create table definition, I agree with you that the "CREATE TABLE AS" command would not help too much.

In this case, one thing that comes to my mind is to query the view "DBC.COLUMNS", where you will find all column definitions for the system, and compose the CREATE TABLE as you wish.

Good Luck!