We help IT Professionals succeed at work.

Powershell: update sql table from powershell query input

Hi,

I have a PowerShell script which does complicated queries to sccm on a daily basis.
I d like to create a scheduled job which puts the output in a Microsoft Sql database.
I can create the initial database and fill.it up with data but how to update it correctly via sql?
So that would be $sccmdata | foreach item
If  not exist delete, if exist update if not exist create
Please help with the sql query and ps script
J
Comment
Watch Question

I think the guy covers everything you need (INSERT and UPDATE):

https://virtuallysober.com/2017/07/10/working-with-sql-databases-using-powershell/

HTH

Author

Commented:

Not at all as simple as that.
It should compare source with target: what has changed, howto define that? Say you have softwarevendor,softwarename,softwareversion in source and destination, how you define if there is something changed?

Once you know, you have to update when existing, delete when not existing in source (delete query is not there in your example), create when not existing in destination.

Then you need to have logging to see what is changed before you

effectively will change, so you know the update works.

Also a summary is needed to define what has changed.

Not sure if I understand all of your requirements. But you should be able to use either SQL MERGE or PowerShell.


SQL MERGE:

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

PowerShell:

You can use the Compare-Object PowerShell command to compare your tables. Never used it but looks promising,
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/compare-object?view=powershell-5.1

A DELETE is like a SELECT *.
When a SELECT * returns the rows you want to delete, replace SELECT * with DELETE.


PowerShell's Add-Content will help you writing a log file.

Author

Commented:

Thanks, but again: not as simple as that: the powershell us getting data from a table, if the date is changed vs target sql, it should execute powershell (which get details on item), that data should be written to target sql.

So not just comparing two sql tables/db