CURSORS

Question

Post reply

CURSORS

gilbert delarosa

SSC-Addicted

Points: 425
More actions
March 12, 2010 at 3:49 pm

#139320

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GTR SSCrazy Eights Points: 8007 More actions · Answer 1

Yes, you can use temporary tables as an alternate depending on your goal.

EnjoY!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

Paul White SSC Guru Points: 150468 More actions · Answer 3

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
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)