如何使用PowerShell删除SQL记录

发布于 2025-01-22 07:25:59 字数 790 浏览 0 评论 0原文

我有一个简单的脚本来比较两个表，我只是想知道如何修改它，以便如果hold_inv表有差异，它将删除它。

因此，基本上我的temp_hold_inv包含最新数据，我想始终与我的hold_inv表同步。

假设从temp_hold_inv表删除特定用户比较。

在这里，我能够获得差异，但不确定如何使用$ campelult删除与hold_inv和daily_proc表的所有差异的所有差异

$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

。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一曲爱恨情仇 2025-01-29 07:25:59

您需要一个Merge语句，但是您需要确定表的主要键是为了匹配行。

$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

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

回复收藏 0 原文

~没有更多了~