janhoedt
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/
PowerShell:
You can use the Compare-Object PowerShell command to compare your tables. Never used it but looks promising,
https://docs.microsoft.com
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.
SQL MERGE:
https://www.mssqltips.com/
PowerShell:
You can use the Compare-Object PowerShell command to compare your tables. Never used it but looks promising,
https://docs.microsoft.com
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.
ASKER
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
ASKER
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.