everyone! I'm mostly just curious if this can be done, but here we go: I have a query I run about once a month that ends up going into an Excel Worksheet. It creates a list of businesses and representatives. Now, I know that you can create SQL code that exports query results directly to a worksheet (see Export SQL query data to Excel), but I want to know if I can take the result, and export it using SQL to multiple worksheets based on representative? So that each rep gets a worksheet of just their businesses? I'm thinking it would involve some kind of loop (see SQL Server FOR EACH Loop), but I just can't find anything definitive. Thoughts? Thanks!
Asked
Active
Viewed 734 times
1 Answers
0
You can create an SSIS package to achieve that. Write a different query for each rep and export the results into an Excel.
Varun Mehta
- 134
- 10
-
I mean, if I knew the individual reps, sure, but the problem is that I don't know from month to month who the reps are, because they might change. Or are you saying find out who the reps are, then write the queries? Sorry! I should've put that part in my question :/ – MLangford May 12 '17 at 20:16
-
Ok I get your point now. So you have different set of representatives each month. I guess what you can do is.- 1. Write a query to find out all the distinct representatives and store the result in a temporary table. 2. Loop through the records in temporary table and run a query with where clause having the rep_id. 3. Save the result in excel file. – Varun Mehta May 12 '17 at 20:35
-
You can also make use of Foreach loop container in the SSIS package. – Varun Mehta May 12 '17 at 20:42
-
Man. I'm an idiot. I didn't even think about storing distinct reps in a temp table. Thanks! – MLangford May 12 '17 at 20:51
-