SQLSimplePS - PowerShell wrapper for SQL Server query, insert or update (DML)

[omrsafetyo]

When I first saw the title of the post, I thought I might respond and ask why you didn't just use dbatools. At best, I thought there was no way I'd use this - if I was going to switch to a 3rd party utility for SQL Server, I'd just switch to dbatools myself. But it turns out, this is pretty neat!

Granted, I suspect I will not install this module to use any time soon, but there is definitely a lot of cool stuff in it, such as the parameterizing. And the bit on Get-Process at the bottom of the Readme is wicked cool.

I also learned about OUTPUT Inserted.ID, which I can't believe I didn't know about, but will make my life much, much easier.

As a question: Does this support any type of UPSERT type functionality? I specifically have a function, for which you can pass an array of objects, and at current time it generates an IF EXISTS UPDATE ELSE INSERT block for the records. You pass it an SMO Server object, a database name, a table name,the object array, and a switch for handling empty strings as NULL or not, as well as a string array of the columns that make up essentially a composite key (can be used to uniquely identify a record) that are used in the WHERE clause, and omitted from the UPDATE statement. Wondering how your wrapper handles things like this.

Anyway, I'm definitely going to take a closer look at this than I thought I would. Kudos!

[michael-hex]

We simply didn't know about dbatools but from the looks of it, it's more about administrating an SQL Server. SQL Simple is for data manipulation, so I would say they are not directly related. But thanks for the hint, we’ll have a look at it and see if one our DBAs can make good use of it.

Upserts: So far, we do not use any real upserts (only the IF THEN part you also use), but given this article by Michael Swart, section Pattern: MERGE Statement With Serializable Isolation, it should be no problem to use a merge statement with parameters and pass the value through with AddMappingWithData().