Loop through data table and run SQL script on each value

Help Studio
, ,
1

I have an excel that I output to a data table. I want to loop through the data table, use the value in column “Client Num” to do a look up in SQL table and retrieve the value match in column “Employee Number” from the SQL table.

Few questions:

  1. is my string from the data table correctly written into the parameters to pass into the SQL query?
    image
    image1008×386 17.3 KB

image
image785×319 11.3 KB

  1. how do i write the data back to the original data table (dtReportOutput) in a new column called Employee Code each time it loops through the results? I’m guessing I have to write it to a data table and then write that data table to excel as final output but I’m not 100% sure. Or there might be a better solution.
2

Hi @atarantino

Try this SQL Query:

SELECT [Employee Number] FROM YourTable WHERE [Client Num] = @ClientNum

Hope it helps!!

3

Thanks. My SQL query works fine. I’ve tested in SQL server manager. I get the result i need. It’s more about making sure it works with the parameter I am using and most importantly, how to write the results after the query is ran. I need to all back into the excel (as a final result) and I’m struggling to get there.

4

Hi @atarantino

  • Add Data Column (Add “Employee Code” column to dtReportOutput)

For Each Row in dtReportOutput

  • Execute SQL Query and retrieve “Employee Code”
SELECT [Employee Code]
FROM Employees
WHERE [Client Num] = @ClientNum
  • Assign retrieved “Employee Code” to current row in dtReportOutput
CurrentRow("Employee Code") = employeeCode

End For Each

  • Write Range (Write dtReportOutput back to Excel)
5

the out put for Run Query is a data table not a string. can i convert it to a string? i tried putting .tostring at the end but it comes up blank in my output panel.

6

It would be better to load the entire SQL table into a datatable variable, then use the Lookup Data Table activity to do your lookups. That’s more efficient than repeated SQL queries.

7

Hi @atarantino

Use Output Data Table activity. This will convert your dataTable into string. Store that in an variable and print it in Message box.

Regards