How do you merge two different SQL Server 2012 database tables in single stored procedure?

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

Since:

target_table cannot be a remote table. target_table cannot have any rules defined on it.

What you could do is first insert all the data from your linked server to your current server database table using four-part query, then do Merge.

OR:

using source table as remote table because remote table is supported in USING. So what you could do alternatively is:

first Change connection to [160.80.3.220].[sample]

then:

MERGE [dbo].[Products] AS TARGET

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