Export output of each sql statement (read from a input file) in different file using BTEQ

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

Export output of each sql statement (read from a input file) in different file using BTEQ

Hello Everyone,

 

I have a question, how can we export the output of each sql statement [read from a input file using .run file command] into a different file using BTEQ.

 

For e.g.

 

If I have input file input.txt which has two statements:

sel count(*) from dbc.tables;

sel '1';

 

When I read this input file throught BTEQ using .run file = input.txt

We should get two files say out1.txt and out2.txt containg output for each query.

 

I am able to extract in 1 file using .export command

 

I don't want to break input file in multiple files containig one statement each and then export.

 

Please let me know if there is a way to achieve this.

 

Thank you. Have a great day.

 

4 REPLIES
Senior Apprentice

Re: Export output of each sql statement (read from a input file) in different file using BTEQ

Hi,

 

Using BTEQ, this has to be done in your 'input.txt' file.

 

That needs to contain:

.export file = out1.txt;
select * from dbc.tables;
.export reset;
.export file = out2.txt;
sel 'l';
.export reset.

...and so on.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Export output of each sql statement (read from a input file) in different file using BTEQ

How is the IMPORT file created, can you simply add the .EXPORT command to it?

.EXPORT FILE = out1.txt;
sel count(*) from dbc.tables;
.EXPORT FILE = out2.txt;
sel '1';

 

Enthusiast

Re: Export output of each sql statement (read from a input file) in different file using BTEQ

Thank you for your answer.

The thing is I am not sure about number of statements in the input file and I was wondering if there is a way in which we can repeat a block of statements (with may be say like .repeat * )with a variable which can increment and create files. Since in other case we need to append export and reset statements before every sql statement in input file or may be I don't even have write access to that file.

Appreciate your time.

Senior Supporter

Re: Export output of each sql statement (read from a input file) in different file using BTEQ

bteq is very limited on this kind of functionality.

you need either start shel scripting to generate BTEQs or maybe the better approach is to switch to the teradata python module

check https://developer.teradata.com/tools/reference/teradata-python-module

Ulrich