MSSQL merge with distributed transaction alternative <p>I have a distributed transaction where I need to merge into the target remote table. Now MERGE INTO isn't allowed according to MSDN: “target_table cannot be a remote table”.</p> <p>So my workaround goes as follows: 0. begin distributed transaction 1. define a cursor 2. open it 3. if cursor has at least one record (CURSOR_STATUS()=1) fetch next 4. if exists (select top 1 * from target_remote_table where id = @myCurrentCursorId) -> when true update target_remote_table when false insert into target_remote_table 5. commit/rollback distributed transaction depending on trancount and xact_state</p> <p>It works but I know that cursors are evil and you shouldn't use them. So I want to ask if there is any other way I could solve this by not using cursors?</p> <pre><code>USE [My_DB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[my_proc_merge_into_remote_table] @ID_A INT, @ID_B INT AS BEGIN SET NOCOUNT ON; -- CURSOR VALUES DECLARE @field_A INT DECLARE @field_B INT DECLARE @field_C INT DECLARE @field_D BIT DECLARE @field_E INT DECLARE @field_F DATETIME DECLARE @field_G VARCHAR(20) DECLARE @field_H DATETIME DECLARE @field_I VARCHAR(20) BEGIN TRY BEGIN DISTRIBUTED TRANSACTION -- CURSOR !! DECLARE my_cursor CURSOR FOR SELECT b.field_A , b.field_B, c.field_C, a.field_D, a.field_E, GETDATE() AS field_F, a.field_G, GETDATE() AS field_H, a.field_I FROM dbo.source_tbl a LEFT JOIN dbo.base_element_tbl l ON a.obj_id = l.obj_id AND a.element_id = l.element_id INNER JOIN dbo.base_obj_tbl b ON a.obj_id = b.obj_id INNER JOIN dbo.element_tbl c ON a.element_id = c.element_id WHERE a.ID_B = @ID_B AND a.ID_A = @ID_A; OPEN my_cursor; -- check if cursor result set has at least one row IF CURSOR_STATUS('global', 'my_cursor') = 1 BEGIN FETCH NEXT FROM my_cursor INTO @field_A, @field_B, @field_C, @field_D, @field_E, @field_F, @field_G, @field_H, @field_I; WHILE @@FETCH_STATUS = 0 BEGIN -- HINT: MY_REMOTE_TARGET_TABLE is a Synonym which already points to the correct database and table IF EXISTS(SELECT TOP 1 * FROM MY_REMOTE_TARGET_TABLE WHERE field_A = @field_A AND field_B = @field_B AND field_C = @field_C AND field_E = @field_E) UPDATE MY_REMOTE_TARGET_TABLE SET field_D = @field_D, field_H = @field_H, field_I = @field_I; ELSE INSERT INTO MY_REMOTE_TARGET_TABLE (field_A, field_B, field_C, field_D, field_E, field_F, field_G, field_H, field_I) VALUES (@field_A, @field_B, @field_C, @field_D, @field_E, @field_F, @field_G, @field_H, @field_I); FETCH NEXT FROM my_cursor INTO @field_A, @field_B, @field_C, @field_D, @field_E, @field_F, @field_G, @field_H, @field_I; END; END; CLOSE my_cursor; DEALLOCATE my_cursor; IF (@@TRANCOUNT &gt; 0 AND XACT_STATE() = 1) BEGIN COMMIT TRANSACTION END END TRY BEGIN CATCH IF (@@TRANCOUNT &gt; 0 AND XACT_STATE() = -1) ROLLBACK TRANSACTION END CATCH; END </code></pre>