In SQLMessenger, you can configure an SQL query within an attachment template, then pass different query parameters to this SQL query. Each query result can be generated as a separate Excel file, and these files can be merged into a single Excel attachment file. This feature is called Loop Template.
Use the Loop Template to query sales reports for 5 cities and merge them into one Excel file
2 Set the Loop Template Option
(1) Select the option "Generate attachment files repeatedly according to the specified list (Loop Template)" in the template options. If you want to merge the reports into one Excel file, you also need to check the option "Merge the Excel attachment files generated by the template into one Excel file". Then click the "List Setting" button.
Select the Loop Template option
(2) In the "Template Loop List Setting" dialog, select the file containing the list of cities to be queried.
Select the city list file
If the city list is in your database, you can also read the city list from the database.
Read the city list from database
3 Use the @@LoopData Variable in the SQL Query
Add SQL query to the template to query sales reports for each city. Use the "@@LoopData(City)" variable in the query statement as the query condition.
Add SQL statements to the template
After configuration, during task execution, the SQL query of the template will be executed once for each record in the "Loop List". The "City" field of each corresponding record will be passed as a query parameter to the SQL query. The query results will then be converted into an Excel file. After all city reports have been queried, the generated reports will be merged into one Excel file.
Note: You need to install Excel 2010 or a later on your computer to merge Excel files.
Using SQL Statements in Tasks, Using Variables in Tasks, Using Simple Tables, Using SQL Query in Templates