sql server将输出结果插入到现有表中

ovfsdjhp  于 2021-07-26  发布在  Java
关注(0)|答案(0)|浏览(235)

我有一个从多个表中删除记录的事务。我想在一个名为deletelog的不同表中存储每次删除的记录。
我的交易:

-- SET THESE VARS TO CLIENT INFO
DECLARE @OrganizationID INT = 25
DECLARE @AreaID INT = 1181
DECLARE @ExpID INT = 43926

BEGIN TRANSACTION [Tran1]
  BEGIN TRY

    -- EXP HISTORY.
    DELETE [ExpHistory] OUTPUT DELETED.*
    FROM [ExpHistory] 
    INNER JOIN [Area] ON [ExpHistory].AreaID = [Area].ID
    INNER JOIN [Organization] ON [Area].OrganizationID = [Organization].ID
    INNER JOIN [Exp] ON [ExpHistory].ExpID = [Exp].ID
    WHERE [ExpHistory].AreaID = @AreaID AND [Organization].ID = @OrganizationID AND [Exp].ID = 
    @ExpID

    -- INSERT OUTPUT INTO EXISTING TABLE CALLED DeleteLog

    -- EXP.
    DELETE [Exp] OUTPUT DELETED.*
    FROM [Exp] 
    INNER JOIN [Area] ON [Exp].AreaID = [Area].ID
    INNER JOIN [Organization] ON [Area].OrganizationID = [Organization].ID
    WHERE [Exp].AreaID = @AreaID AND [Organization].ID = @OrganizationID AND [Exp].ID = @ExpID

    -- INSERT OUTPUT INTO EXISTING TABLE CALLED DeleteLog

  COMMIT TRANSACTION [Tran1]

END TRY

BEGIN CATCH

  ROLLBACK TRANSACTION [Tran1]

END CATCH

output子句按预期返回已删除的项,但现在我想将它们插入到deletelog表中。我需要设置特定的列,所以我不相信使用into会起作用。我认为合并是一个潜在的解决方案。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题