Export multiple Sql Results into single file using bteq export

Database
Highlighted
Enthusiast

Export multiple Sql Results into single file using bteq export

Hi All,

Greetings!

 

 

I  need to write a bteq scipt where i need to write  all the sql result into to a single file.

Thanks in advance.

 

 bteq has below sql statements

 

eg

Select  * from table_1;

Select  * from  table_2;

Select * from table_3;

-----------------------------------------------------------------------------------------------------

file o/p should with headed as below

-----------------------------------------------------------------------------------------------------

Column1~Column2~Column3~Column4~Column5

value1~value2~value3~value4~value5

 

Column1~Column2~Column3~Column4~Column5

value1~value2~value3~value4~value5

 

Column1~Column2~Column3~Column4~Column5

value1~value2~value3~value4~value5

 

 

Thanks,

Vinoth

Tags (1)
4 REPLIES
Senior Apprentice

Re: Export multiple Sql Results into single file using bteq export

Hi,

 

If you want multiple outputs into a single file then just use a single .EXPORT command.

 

In your case you'll want to use ".EXPORT REPORT..."

 

You'll also need to use the following commands .SET SEPARATOR and  TITLEDASHES .

 

HTH

Dave

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

Re: Export multiple Sql Results into single file using bteq export

Thanks Dave its working fine 

 

Need one more help is there any option to have the select statement along with the result set  as below

exported file as below

if so please share withe sample code

eg

sel * from table1;

col_1~col_2~col_3

valu_1~value_2~value_3

 

sel * from table2;

col_1~col_2~col_3

valu_1~value_2~value_3

 

 

Senior Apprentice

Re: Export multiple Sql Results into single file using bteq export

Hi,

I don't think you can do exactly what you're asking.

 

I think the closest you will get is to have all of the BTEQ output written to the same file, AFAIK you cannot get only the sql and the results.

To do this remove the EXPORT command from the script and change the command line to something like:

bteq <scriptfile.txt >scriptfile.log &2>1

 

This will write all of the BTEQ output to file 'scriptfile.log', including success/error messages, activity counts etc. Not quite what you've described, but as I said I think the closest you will get.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Export multiple Sql Results into single file using bteq export

Hi. If you could provide some context for your questions that would help us more efficiently help you. You might not even be using the best tool for what you are ultimately trying to accomplish. That being said...

An export file can be appended to. So one way to accomplish what you want would be to put each SQL into its own RUN file and use a .OS command to issue something like a "cat" and redirect those results into the export file before running each file. So the sequence could be something like this :

.OS cat mysql1.run > myexport.results

.EXPORT file=myexport.results

.RUN file=mysql1.run

.EXPORT RESET

.OS cat mysql2.run >> myexport.results

.EXPORT file=myexport.results

.RUN file=mysql2.run

.EXPORT RESET

Another alternative could be to use the HEADING and FORMAT commands. You would be limited to a max of 10 lines (each ~ 253 single-byte characters ) for the heading and it would be centered. Those 2 commands could look something like this:

.FORMAT ON

.HEADING "select *//from table2;"

If you have more questions, please provide more context .  :)