Use SQL table and process

[psdarwin]

I’m not a SQL expert, but it seems to me like you are trying to write PowerShell like it’s C#. @dotnVo had an example that uses Invoke-SQLCMD, which is much simpler and more like native PowerShell. With that, you don’t have to be concerned with managing database connections.

I’d encourage you to look at the help for Invoke-SQLCMD and see if that moves you in a better (simpler code) direction.

Invoke-Sqlcmd (SQLServer)

[andyh]

@dotnVo

$Output = Invoke-SQLCMD -Query "Select * from ctssums" -ServerInstance $ServerInstance -Database $Database 

$Output | ForEach-Object {
$query = $($_.sqltoexec)
 Invoke-SQLCMD -Query $query -ServerInstance $ServerInstance2 -Database $Database2
}

Yes I did run that example but how can it be made to handle groups of records
ID > ? and ID < ? and catch failures ?

Thanks

[dotnVo]

Andy,

1. This was an example. Notice the chain of events. You added the ‘batch requirement’ after I gave some starter code for you to try. I then had to ask to follow up questions attempting to get information from you that you’ve minimally responded about. Pretty much anything is possible, code it. I gave you some ideas (Because I have no idea how, why etc. you are trying to batch, not clear what problem you are attempting to solve or pre-optimize), so I gave you a few things to think about. You’re constantly changing the requirements, and perhaps not intentional, it really seems like you are trying to people to write the code for you. This is why it’s so crucial for you to write the code and fully explain all the things you need to do using details examples, etc.
2. As previously mentioned - I told you that anything is possible, you just need to code it. Instead, you wrote a query into an LLM and it spit out SQL/.NET, with a large part of that logic being handled by a SQL query. Now you’re asking for help on that code because it’s not doing what you want, despite my encouragement if you writing the code using PS. Just a reminder this is a PowerShell forum.

So I write again, repeating that you can write PowerShell to process only certain records or process many in parallel. You never elaborated on why or what problem you were tryin gto solve by ‘batching’. You also didn’t clarify by going to grab 50K at a time, if you were trying to then run the insert commands all at the same time (run them in parallel). This is why i asked questions to see what problem you were trying to solve. I feel like what you’re really asking is you’re trying to execute in parallel records in 50K batches. I think you’re trying to make it go faster because it’s lots of records. You never answered the why, despite me asking. You can do that with PS but again you probably have to use runspaces or ForEach-Object -Parallel in PS7. You likely can use SQL to do it too (that might be what the SQL code above does, but most of us don’t know SQL well enough here to help you on SQL queries that are complex).

Nontheless, the moral of this thread is - you need to code it. Do some research on PowerShell on Looping, conditionals, paralellization, etc. if you want to use PowerShell. You may have to marry that with SQL. If you want to use SQL to do the coding because it makes more sense, that’s okay too. However, for SQL centric questions this isn’t the appropriate place to get help for those technologies. It’s also not the appropriate place for full coding solutions.

I’m going to close this thread as I think we’ve done all we can offer on this one… More than enough resources in PS have been provided that satisfy the questions thus far. Andy if you want to open a new thread, please reach out to me in a DM, as I think we’ll need to continue the conversation of making sure to provide proper context, details, etc. prior to creating the next thread.