Toad World® Forums

Best practices re: stringing together SQL scripts?

Hi everyone. I am looking for the “best practice” way to string together a series of SQL scripts (on SQL Server and MySQL 5.0). For now, assume that I want to use TDA Automation to do that (v. stringing them together with a master script or individually in Windows Scheduler).

Some of the SQL scripts are updates / inserts / deletes, some are selects that shouldn’t return any rows (unless something went wrong), some are selects that should return rows. Assume one .SQL file per statement. I’d like to be able to log the # of records affected by the update/insert/delete scripts. I’d also like each script to append to a plain text log file when starting and when ending (including timestamp, # recorded affected / returned, etc.)

I’m guessing that there is a generally-accepted set of best practices on how to string together the SQL scripts and how to log the scripts, so I’d welcome any advice from you TDA gurus! I also know there is probably a way to structure each SQL script to make logging easier (i.e., in SQL Server, I end each statement with a semi-colon and then a “GO” … maybe that’s not good practice or maybe it prevents results from being logged)?

One simple way is to use the Execute script activity with the row count variable. If you give a name to the row count var it will write to the log file how many rows were affected. You could add one script per SQL file and have all data in one log file. Send yourself an email at the end with the log file attached or you can embed the log file into the content of the email.

Debbie