0

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

3
  • 1
    Have you considered a PowerShell script instead of doing it all in T-SQL?
    – Dan Guzman
    Jan 30, 2021 at 1:32
  • Is PowerShell alot better than something like Python for working with TSQL? I know python but not powershell.
    – Lightsout
    Feb 1, 2021 at 19:22
  • Use any scripting tool you are comfortable with that can execute SQL queries and process results. I mentioned PS because it's already available with Windows and can use .NET framework SqlClient similarly to c# so you don't need to install anything else.
    – Dan Guzman
    Feb 1, 2021 at 19:22

4 Answers 4

0

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.

2
  • I was thinking of putting in the IP address into a table and somehow loop through that if thats possible, but if you are saying copy pasting 30x is better performance then forget it.
    – Lightsout
    Jan 30, 2021 at 1:22
  • @bakalolo Even with the IP address in the table, you won't be able to use them to select data from in a SQL loop to run the query unless you build a Dynamic SQL string and then execute it.
    – J.D.
    Jan 30, 2021 at 1:24
3

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

1
  • I am using linked server to connect, I just want to iterate over each IP address using cursor and connect to each using linked server.
    – Lightsout
    Feb 1, 2021 at 19:10
1

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".

1
  • How does try catch sound for error handling with cursor and procedure to connect to each server? I would like to ideally skip over failed connection and print a message or something.
    – Lightsout
    Feb 1, 2021 at 21:10
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.