Automating Dynamic Query

Toad Data Point
1

Hello

This is my first crack at using the automation tool in Toad so please bare with me. Anyhow, I’ve written a query for a report my customer would like run every month or so.

For argument sake, assume I had the following query:

SELECT * FROM ARTISTS

WHERE ArtistID IN (insert long list of IDs which are alphanumeric)

My customer will be providing me with a list every month or whenever she wants to run the report so this can’t be hard coded into the query.

How can I make the query run every month, and use the different ArtistIDs or variables? Also is there a way that this could be done in reverse from excel?

I’ve looked at the following link:

http://www.toadworld.com/products/toad-data-point/b/weblog/archive/2013/07/24/toad-data-point-automation-series-blog-12-variables

because I’m on v4.3 I don’t see a lot of the screens she shows in her illustration. Would appreciate the help.

2

hello bobmurdock,

Here is my suggestion:

  1. First of all I believe you should use some local instance of database (ex: MS SQL, MySQL) where you can create a simple table [myArtists].
  2. Change your query to select * from artists as a join myArtists as m on m.ArtistID = a.ArtistID
  3. Add Execute Script activity as first step in your automation script and execute TRUNCATE TABLE myArtists
  4. Add ImportWizard activity as second step and import the .csv file from customer’s list of artist ID’s into myArtists table
  5. Then finally using SelectToFile or ExportWizard activities to export the results back into excel or whatever output you desire.
  6. Maybe you might add SendEmail activity as the very last step to notify some recipients (customer, you on cc, your boss, etc.)
  7. Then it’s only a matter of keeping the automation script scheduled on your PC (you must keep it online at the time of schedule is hit) or a server that is alive nonstop.
  8. Or disabled scheduled but just once a week opening the automation and manually hitting F5 key to execute and you are done.
    Please note that using variables is more for smaller sized parameters.

Martin

3

Hi Martin

Thanks for the quick response, this will surely help. A few questions:

  1. Can this be done through a temp table?

  2. Very possible that I don’t have the permission to use the TRUNCATE function. Is there an alternative?

—Also, when you say smaller parameters, are you referring to the ArtistIDs my customer will add to the list? or you’re speaking generally in reference to the conditions

I’d otherwise apply to the query? Thanks again for the help.