BTEQ to connect to remote Teradata db and execute multiple queries to different files

Tools & Utilities
Enthusiast

BTEQ to connect to remote Teradata db and execute multiple queries to different files

I am new to BTEQ. I request some of the experts help to answer my questions below.

I have a requirement to connect to a remote teradata server using shell script. Can I use BTEQ for this purpose? 

Also I need to execute a set of sql queries and export the outputs to  different files in excel format to the local system. Is that possible as well?

Please help..

5 REPLIES 5
Teradata Employee

Re: BTEQ to connect to remote Teradata db and execute multiple queries to different files

Hi Ricky_r,

 

Yes totally.

There is an EXPORT option in BTEQ to output a query in a file.

Don't forget to EXPORT RESET at then end.

 

Enthusiast

Re: BTEQ to connect to remote Teradata db and execute multiple queries to different files

Thankyou Waldar.

For writing the output queries to multiple files in local system , would the below bteq script format work?

What I am trying to do is ,exporting  the table1 output to 'output1.xlsx' and table2 output to 'output2.xlsx'

If not, would you help me with the right way to do it?

 

.logon {remoteurl}/{username},{password};

.export dif file=C:/tmp/output1.xlsx

select * from table1;

.export dif file=C:/tmp/output2.xlsx

select * from table2;

.export reset

.logoff

.quit

Teradata Employee

Re: BTEQ to connect to remote Teradata db and execute multiple queries to different files

You're missing an export reset I'd say :

.logon {remoteurl}/{username},{password};

.export dif file=C:/tmp/output1.xlsx
select * from table1;
.export reset

.export dif file=C:/tmp/output2.xlsx
select * from table2;
.export reset

.logoff
.quit
Highlighted
Enthusiast

Re: BTEQ to connect to remote Teradata db and execute multiple queries to different files


@Waldar wrote:

You're missing an export reset I'd say :

.logon {remoteurl}/{username},{password};

.export dif file=C:/tmp/output1.xlsx
select * from table1;
.export reset

.export dif file=C:/tmp/output2.xlsx
select * from table2;
.export reset

.logoff
.quit

Thankyou , it worked.

But in the output generated , the column titles are missing. How can I include that in the output file?

Also , the format looks to have changed in the output. Is there any way to get the output in the same format(especially the dates) as the Teradata table?

Thanks in advance.

 

Teradata Employee

Re: BTEQ to connect to remote Teradata db and execute multiple queries to different files

You can use the DATALABELS option :

.export dif datalabels file=C:/tmp/output1.dif

I learned in the process that if I use XSLX as an extension Excel 2013 fails to read the file, but it's ok with DIF extension.

 

About the format, I think it's better for you to control it at the SQL level using cast, to_char, format.