Question :
I have to do the following 30 times
connect to server with unique IP using TSQL
download a table to temp table
do some query on the temp table (same query 30times)
at end union all the tables for a final solution
Would loop or cursor or some other approach be a better approach? I don’t want to copy paste similar SQL 30 times but that is also an option.
I am currently using linked server to connect to each server
Answer :
If you want best performance then using the UNION ALL operator 30 times is probably more efficient because it’s more relational of a solution than using either a loop or cursor. If you don’t care about performance, then a loop will be simpler to implement than a cursor.
Though I’m not sure how you plan to dynamically change out which server you’re pulling data from in a loop or cursor unless you’re building a Dynamic SQL string and then executing at the end, or by loop you meant in a procedural language.
Connecting to other instances without Linked Servers is not convenient unless you use Registered Servers or Central Management Server.
If you have registered servers or CMS, you can execute the same query against all the instances in a group (or all registered servers) at once via SSMS (see the links above). But I don’t know if you can output the results to a single table in one instance; you may be stuck with copy & paste to Excel.
If you’re willing to go with Powershell, the dbatools module has a very convenient solution in Invoke-DbaQuery.
Invoke-DbaQuery -Query "select fields from table" -SqlInstance instance1,instance2,instance3 will return an object representing the results of the query plus a field with the corresponding instance name that each returned record came from.
From there, you can filter the results with Where-Object, or you can copy the results into a table in another instance with Write-DbaDbDataTable
I think that what’s more important than if you use a cursor or loop — they’re the same thing, after all — are a couple other things:
- Error handling
- Query performance
Error handling, because if you think running the same query on 30 different servers is annoying, having to run the same query 30 times because it keeps failing at different points will be just as bad.
Likewise, no matter how you approach executing the query across your chosen targets: If it runs slowly, any triumph felt from making an initial decision will be quite fleeting. It’s bad enough waiting a minute for one query to finish. Multiply that by 30 and, well, I think in math they call it “boring”.
Is exactly for this scenario that I wrote the tool QueryLauncher
Is written in PowerShell.
Basically your drop all your .sql files in a folder and QueryLauncher will run those for you.