All Forums Database
TData_Why 2 posts Joined 02/10
03 Feb 2010
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

TData_Why 2 posts Joined 02/10
03 Feb 2010

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

dnoeth 3266 posts Joined 11/04
04 Feb 2010

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

Dieter

You must sign in to leave a comment.