Extractin a QUERY TEXT from a Column and using this output we need to insert the data into another table.

Database
Enthusiast

Extractin a QUERY TEXT from a Column and using this output we need to insert the data into another table.

Hi,

I have a requirement  which is a following

I have a table XX which has a column QUERY_TXT which contains a select query .




QUERY_TXT 
select * from xxx

 Now I have to extract the query form the above table and use that as my select statement to populate another table using BTEQ ,some thing like this

insert into xxxx

select * from xxx ;

Quich help is really helpfull

Thankyou in advance

6 REPLIES
Enthusiast

Re: Extractin a QUERY TEXT from a Column and using this output we need to insert the data into another table.

Hi,

How many rows / Select statements are there in QUERY_TXT? 

Do you have to repeat this BTEQ for all the rows in QUERY_TXT?

Khurram
Enthusiast

Re: Extractin a QUERY TEXT from a Column and using this output we need to insert the data into another table.

Hi,

What I did is something liks this to get the insert into :

select 'insert into '||trim(databasename)||''||trim(tablename) from dbc.tables where tablename='raja_test' and databasename='xxxx';

In conjunction, I feel you need to have a matching condition of insert into... against the select * from xxx., something similar( just  pseudo )

select 'insert into'||trim(databasename)||''||trim(tablename)||'' ||trim(from dbc.tables(say) a.,dbc.qrylog (say) b where tablename=raja_test; and databasename=xxxx ......

where a.tableid1=b.tableid1....

(hint :you can enter an order such that you can match one table against another)

Hope you get the logic.

For a small number of tables, you can use export and then use along with excel, it is quite handy

Cheers,

Raja

Enthusiast

Re: Extractin a QUERY TEXT from a Column and using this output we need to insert the data into another table.

RAJA : Thankyou for the quick response .

Khurram : We have to repeat this for all the rows(50 approx) to take the back up in BTEQ .

Regards

Anvesh

Enthusiast

Re: Extractin a QUERY TEXT from a Column and using this output we need to insert the data into another table.

RAJA : I am not able to figureout the logic can you please be a bit more clear on this

Regards

Anvesh

Enthusiast

Re: Extractin a QUERY TEXT from a Column and using this output we need to insert the data into another table.

I hope you understand this logic 1:

 select 'insert into '||trim(databasename)||''||trim(tablename) from dbc.tables where tablename='raja_test' and databasename='xxxx'; ---It gets you the statement

insert into yourdbname.yourtablenam  Note :You can use IN  instead of = sign

Since I can see you have only around 50 rows, you can use excel against your queries and then paste the whole code into a bteq and run.

I tried for one row and it works as below:

select 'insert into  '||trim(t.databasename)||'.'||trim(t.tablename)||a.id from dbc.tables  t,xxxx.raja_test a where t.tablename='raja_test' and t.databasename='xxxx'

and t.tablename=a.tablename

ct xxxx.raja_test(id varchar(5000),tablename varchar(30));

insert into xxxx.raja_test('select (*) from abc','raja_test');

Hope you will try at least :).

Cheers,

Raja

Senior Supporter

Re: Extractin a QUERY TEXT from a Column and using this output we need to insert the data into another table.

you can export the SQLs (as full Insert/selects) with bteq and can import and call these generated SQLs in the same job.

Check http://forums.teradata.com/forum/general/bteq-script-to-read-and-execute-query-from-a-table