Toad World® Forums

Automating a parameterized SQL query


#1

Hi,

I am new to TDA and would like to automate the following using the automation capabilities of TDA 2.7:

I have a SQL Query that would pull some data from DB2 Subsystem on mainframes.
This query will have two parameters that should be read from a spreadsheet containing the list of values in column A & B.

The query should execute by reading param 1 from column A in excel and param 2 from column B in excel and append the result of the query for each parameter combination to an output excel spreadsheet.

Can anyone help me in this regard?

Appreciate any help on this matter at the earliest.

Thanks,

Supratim


#2

See this blog posting. Particularly the section on loop dataSets.
http://www.toadworld.com/BLOGS/tabid/67/EntryId/498/Automation-Variables-Automation-variables-using-datasets.aspx

Basically you want to define two automation variables. Then use a loopdataset. Execute a query on the excel file. For each row set the values of the two variables to the col a and b from the excel row. Then exectue query with bind values.

When you export the data you will need to export to new worksheet. We currently don’t have append data. (Although it is on our enhancement list). Or you could append to table in a database such as Access.

Debbie