March 12, 2010 at 3:49 pm
Each of my clients has its own database with all of the same obects. I often have to query data for all clients for internal reports. I have been using a cursor for the DB names to go through each DB and insert results into a table that I can select from. Is there a way to do this with out using a cursor or While loop?
March 12, 2010 at 4:20 pm
Yes, you can use temporary tables as an alternate depending on your goal.
March 12, 2010 at 4:26 pm
gilbert delarosa (3/12/2010)
Each of my clients has its own database with all of the same obects. I often have to query data for all clients for internal reports. I have been using a cursor for the DB names to go through each DB and insert results into a table that I can select from. Is there a way to do this with out using a cursor or While loop?
If all of the clients can be reached from the same server, then it becomes a little bit easier.
You could setup a job for each database that dumps the results to a central repository.
You could also query each of the databases with a union statement and a separate select for each database.
You could also create an SSIS package to pull the data for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2010 at 8:09 pm
gilbert delarosa (3/12/2010)
Each of my clients has its own database with all of the same obects. I often have to query data for all clients for internal reports. I have been using a cursor for the DB names to go through each DB and insert results into a table that I can select from. Is there a way to do this with out using a cursor or While loop?
Use SQL Server 2008 SSMS (the servers can be any compatible version). 2008 SSMS enables you to run multi-server queries directly.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 9:44 pm
gilbert delarosa (3/12/2010)
Each of my clients has its own database with all of the same obects. I often have to query data for all clients for internal reports. I have been using a cursor for the DB names to go through each DB and insert results into a table that I can select from. Is there a way to do this with out using a cursor or While loop?
There's nothing wrong with a Cursor or While Loop in such a case because it won't be any kind of burden or performance issue.
You could, however, make a view with UNION ALL. If you're clever, you could automate the creation of such a view so you wouldn't have to manually make a change everytime a new B was created.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply