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!
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.
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!
Yes, that is quite common. It is not a problem.
So I put the drop/create table scripts into a text file and process like below.
.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!
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.