SQL to Create a Table from a View & Retrieve the DDL

Database

SQL to Create a Table from a View & Retrieve the DDL

Hey,

Anyone know how to create a table using a view (Structure & Data)?

Also, is there anyway to retrieve the DDL for the tables or views we have access to?

Thanks,

-Ayyad
4 REPLIES

Re: SQL to Create a Table from a View & Retrieve the DDL

Let me clarify,

1. I would like to copy/replicate the Structure & Data of a VIEW in a database in to a new table in my personal DB Space.
2. I am able to SHOW definition of the View, but it is doing plenty of replacing and joins from other tables that I do not have access to. Is there a way to just grab the Structure, particularly the column names and data types (i.e. Column1 CHAR(6), Column2 VARCHAR(255), Column3 Integer).

I tried the below but i get the error '3853: NEW_TABLE is not a table':
CREATE TABLE THIRTY_DAY_TABLES.NEW_TABLE AS
SAMPLE_VIEWS.ACTUAL_SALES WITH DATA;

I know it is not a table, but could it give me a break and just copy the structure and data :P

Thanks for your help
N/A

Re: SQL to Create a Table from a View & Retrieve the DDL

Hi Ayyad,
you're close:

CREATE TABLE THIRTY_DAY_TABLES.NEW_TABLE AS
(select * from SAMPLE_VIEWS.ACTUAL_SALES) WITH DATA
PRIMARY INDEX (whatever);

Just choose an appropriate PI else it defaults to the first column.

Dieter

Re: SQL to Create a Table from a View & Retrieve the DDL

Hi Dieter,

 

Your Answer is really helpful, But I am facing one issue. I have an existing application which earlier uses syntax like

CREATE TABLE <TABLE NAME> as <TABLE NAME> now we have introduced a view layer in it so now the query generated as

CREATE TABLE <TABLE NAME> as <VIEW NAME>  which is giving me an error that <VIEW NAME> is not a table.

Is it possible that the above query can run just by some modification is DB instead of changing the query so that I do not need to change the existing system.

The above queries are generated to create the work table and stage table.

Teradata Employee

Re: SQL to Create a Table from a View & Retrieve the DDL

No it is not possible.

 

You could change you script to look at the tablekind in dbc tables and substitute the select * format whenever you run into a view.

 

Dave