How to remove sql records using PowerShell

Question

I have this simple script that compare the two table and I'm just wondering how can I modify it so that it'll delete if there is a difference in Hold_Inv table.

So basically my Temp_Hold_Inv contain the latest data and I wanted to always in sync with my Hold_Inv table.

Let say a particular user is removed from Temp_Hold_Inv table but the data is already Existed in Hold_inv and Daily_Proc table then removed from those two table too after comparison.

Here I'm able to get the difference but not sure how to use $comparedResult to delete all the difference from Hold_Inv and Daily_Proc Table

$query2 = "
    SELECT 'Temp_Hold_Inv' AS SRC, T1.*
FROM (
      SELECT Hold, GID, Source FROM Temp_Hold_Inv
      EXCEPT
      SELECT Hold, GID, Source FROM Hold_Inv
      ) AS T1
UNION ALL
SELECT 'Hold_Inv' AS SRC, T2.*
FROM (
      SELECT Hold, GID, Source FROM Hold_Inv
      EXCEPT
      SELECT Hold, GID, Source FROM Temp_Hold_Inv
      ) AS T2
;"


$compareResult = Invoke-Sqlcmd -Query $query2 -ServerInstance $Server -Database $Database 

Any help or suggestion will be really appreciated.

Answer 1

You need a MERGE statement, but you need to work out what is the primary key of the table in order to match rows up.

$query2 = "
MERGE Hold_Inv WITH (HOLDLOCK) AS t
USING Temp_Hold_Inv AS s
  ON t.SomePrimaryKey = s.SomePrimaryKey
WHEN MATCHED AND (
       t.Hold <> s.Hold
    OR t.Source <> s.Source)
  THEN UPDATE SET
    Hold   = s.Hold
    Source = s.Source
WHEN NOT MATCHED BY TARGET
  THEN INSERT (Hold, GID, Source)
    VALUES (Hold, GID, Source)
WHEN NOT MATCHED BY SOURCE
  THEN DELETE
-- you can get output like this
-- OUTPUT $action, inserted.*, deleted.*
;"

Invoke-Sqlcmd -Query $query2 -ServerInstance $Server -Database $Database