如何在单个存储过程中合并两个不同的SQL Server 2012数据库表？

Question

MERGE [160.80.3.220].[sample].[dbo].[Products] AS TARGET 
USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID) 

-- When records are matched, update 
-- the records if there is any change 
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 
       OR TARGET.Rate <> SOURCE.Rate THEN 
    UPDATE 
     SET TARGET.ProductName = SOURCE.ProductName, 
      TARGET.Rate = SOURCE.Rate 

-- When no records are matched, insert 
-- the incoming records from source 
-- table to target table 
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (ProductID, ProductName, Rate)  
    VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate) 

-- When there is a row that exists in target table and 
-- same record does not exist in source table 
-- then delete this record from target table 
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE 

-- $action specifies a column of type nvarchar(10) 
-- in the OUTPUT clause that returns one of three 
-- values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
-- according to the action that was performed on that row 
OUTPUT $action, 
     DELETED.ProductID AS TargetProductID, 
     DELETED.ProductName AS TargetProductName, 
     DELETED.Rate AS TargetRate, 
     INSERTED.ProductID AS SourceProductID, 
     INSERTED.ProductName AS SourceProductName, 
     INSERTED.Rate AS SourceRate; 

SELECT @@ROWCOUNT; 
GO 

Answer 1

时间：

target_table不能是远程表。 target_table不能在其上定义任何 规则。

你可以做的是首先使用四部分查询将链接服务器的所有数据插入到当前服务器数据库表中，然后执行Merge。使用源表作为远程表，因为远程表中USING支持

：

OR。所以，你可以交替做的是：

首先更改连接[160.80.3.220].[sample]

则：

MERGE [dbo].[Products] AS TARGET 

USING [linked server instance].[database].[schema].UpdatedProducts AS SOURCE