Run multiple create-volatile-table queries in Teradata

Database
hxi
Enthusiast

Run multiple create-volatile-table queries in Teradata

Hello,

 

I have a create-volatile-table process that needs to run every week. Currently I login to Teradata SQL Assistance and run them one by one.

 

In order to improve efficiency, is there a tool in Teradata that can run all the queries at once? If so, what the code/scripts would look like?

 

Any help is much appreciated!

 

Thank you!

5 REPLIES
Senior Apprentice

Re: Run multiple create-volatile-table queries in Teradata

Hi,

 

If you already have the code then probably using BTEQ is the easiest option for you.

 

BTEQ reads a plain text file which contains all of the SQl statemenst that you want to execute. The basic format is:

.LOGON tdpid/username,password;

...all of your sql statements here...

.QUIT 0;

You can also add in error checking and (rudimentary) branching.

 

The BTEQ manual is here: https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/Query_Management_Tools/B035_2414_035...

 

HTH
Dave

 

 

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

Re: Run multiple create-volatile-table queries in Teradata

Thank you so much Dave. It's very helpful.

A follow-up question - I created a .txt file that contains all the create-table scripts. In BTEQ, after I do the .RUN FILE C:\filename.txt; I got a message saying ...Encountered EOF on RUN file.

I guess this is normal?

Thanks again for your help!

Senior Apprentice

Re: Run multiple create-volatile-table queries in Teradata

Hi,

Yes, that is quite common. It is not a problem.

Cheers,

Dave

 

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

Re: Run multiple create-volatile-table queries in Teradata

Thanks!

So I put the drop/create table scripts into a text file and process like below.

.logon server/ID

.password

.run file c:\users\username\desktop\filename.txt;Question - Where should I include the error handling? Would it be included in the text file or somewhere else? The error that I get so far is "table does not exist" when bteq is processing the drop table scripts. How should I write the error handling script? Is there an example I can use? Any help is much appreciated. Thanks again!

Teradata Employee

Re: Run multiple create-volatile-table queries in Teradata

There are a few examples in the BTEQ manual.

 

If you simply want to abort the job on error, you can set MAXERROR before the RUN; otherwise you would need to include IF commands in the txt file to handle errors.

You can also use ERRORLEVEL to set the severity of the 3807 "not found", if desired.