All Forums Database
anv526 11 posts Joined 02/12
16 Dec 2013
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
 

M.Saeed Khurram 544 posts Joined 09/12
16 Dec 2013

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

Raja_KT 1246 posts Joined 07/09
16 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

anv526 11 posts Joined 02/12
16 Dec 2013

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

anv526 11 posts Joined 02/12
16 Dec 2013

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

Raja_KT 1246 posts Joined 07/09
16 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
16 Dec 2013

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.