SQL server MERGE 2具有内部联接的源/目标表

本文关键字：目标 MERGE server 2具 内部 SQL | 更新日期: 2023-09-27 18:22:35

我有一个SQL server数据库，其中包含两个表，这些表由一个ID字段连接，如下所示：

Table 1
ID, Selection, Field1, Field2
Table 2
ID, Field3, Field4

表由一个带有级联删除约束的外键连接，因此，如果表1中的字段被删除，表2中与ID匹配的所有字段也将被删除（但您知道）

我有另一个存储过程，它通过选择两个表来提取必要的字段：

    SELECT [Table2].* FROM [Table2] INNER JOIN [Table1] ON [Table1].[ID] = [Table2].[ID] WHERE [Table1].Selection = @selectionParameter

我想从我的c#程序中更新这些表。我以前一直在做以下事情：

DELETE FROM [Table1] WHERE Selection = @selectionParameter
INSERT INTO [Table1] SELECT * FROM @table1Parameter (user defined table type passed in from c#)
INSERT INTO [Table2] SELECT * FROM @table2Parameter

我现在想通过不强制完全删除和重新插入所有字段，而是执行合并来使这一过程更加简化。

到目前为止，我已经在Table1上实现了合并，但似乎无法正确处理与Table2的关系。

到目前为止，我拥有的是：

MERGE INTO [Table1] AS target
USING @Table1Parameter AS source
ON target.ID = source.ID
AND target.Selection = @selectionParameter
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, Selection, Field1, Field2) VALUES (source.ID, etc)
WHEN NOT MATCHED BY SOURCE AND target.Selection = @selectionParameter
    THEN DELETE;

这与以下查询非常相似：SQL Server MERGE+加入其他表

我尝试使用上面的答案，并将上面的relationship-select语句的结果传递到临时表中，但没有成功。

有人能帮我把第二张桌子整合进来吗？

编辑：要求提供完整代码：

USE [MyDatabase]
GO
ALTER PROCEDURE [dbo].[MergeTables]
@Selection int, 
@Table1Parameter udtt_1 READONLY,
@table2Parameter udtt_2 READONLY
AS

然后是上面看到的merge语句。

c#代码只是使用数据表作为参数来执行一个非查询。c#代码目前已完全发挥作用。

SQL server MERGE 2具有内部联接的源/目标表

这是修复，毕竟它确实涉及临时表：

USE [myDatabase]
GO
ALTER PROCEDURE [dbo].[MergeTables]
--declare parameters passed in from c#
--tables coming from c# are edited versions of the SQL tables.
@selectionParameter int, 
@Table1Parameter udtt_1 READONLY,
@table2Parameter udtt_2 READONLY
AS
--merge edits into table 1
MERGE INTO [Table1] AS target
USING @Table1Parameter AS source
ON target.ID = source.ID
AND target.Selection = @selectionParameter
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID, Selection, Field1, Field2) VALUES (source.ID, source.Selection, source.Field1, source.Field2)
WHEN NOT MATCHED BY SOURCE AND target.Selection = @selectionParameter
THEN DELETE;
--define a temp table to hold table 2 editable rows
SELECT [Table2].* INTO T FROM [Table2]
INNER JOIN [Table1] ON [Table1].[ID] = [Table2].[ID] WHERE [Table1].[Selection] = @selectionParameter
--merge edits into temp table
MERGE INTO T AS target 
USING @table2Parameter AS source
ON target.ID = source.ID
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, Field3, Field4) VALUES (source.ID, source.Field3, source.Field4)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;
--place edited or new rows into table 2
MERGE INTO [Table2] AS target 
USING T AS source
ON target.ID = source.ID
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, Field3, Field4) VALUES (source.ID, source.Field3, source.Field4);
DROP TABLE T;

如果需要澄清，请告诉我。它非常复杂，花了我几个小时！希望它能在这种情况下帮助其他人。

相关文章：