Dynamic DDL


Dynamic DDL

I need to create a new table based on the data from a query result. The name of the columns in new table will be the data values from result of a query.

For example, lets say my source query returns following records:

Field1 Field2
V1 A1
V2 A2
V3 A3

Then table DDL should be like:

Create table testdb.test_tab
Start_dt date,
v1 varchar(30),
v2 varchar(30),
v3 varchar(30),
a1 varchar(30),
a2 varchar(30),
a3 varchar(30)
primary index(start_dt);

Currently I am thinking about exporting query result to a file, and append to a file with create table statement, then execute. I am not sure if this will work, have not try it yet.
Please share your ideas/work.

Appreciate you time, thank you.
Tags (1)
Teradata Employee

Re: Dynamic DDL


Please have a look at CREATE TABLE AS statement from the documentation .... that looks perfect solution for what scenario you have mentioned.



Re: Dynamic DDL

3 hours ago
You can run something like this in bteq.

.Export Report File=crtab.txt

.Format off

Select 'Create Table dbname.tblname (Title '')
Select 'Start_Dt Date Not Null' (Title '')
Select ','||Field1||' Varchar(30)' (Title '')
From dbname.anotbl
Order By Field1
Select ','||Field2||' Varchar(30)' (Title '')
From dbname.anotbl
Order By Field2
Select ') Primary Index(Start_Dt);' (Title '')
.Export Reset
.Run File = crtab.txt

You can also do it in SQL Assisstant by running the queries after the setting the option to export all answer sets to a single file and then copy/ paste the answer to the query window to run it.