Issues with SQL Server Merge Statement

Issues with SQL Server MERGE statement

Any of the four values in #S will match your target table's single row value (all values in #S have id = 1 and name = 'A' - so they all match the single row in the target), thus this value will be updated four times - that's what the error says, and it's absolutely right.

What is it you really want to achieve here??

Do you want to set the Address to the first of the values from the source table? Use a TOP 1 clause in your subselect:

MERGE #T 
USING (SELECT TOP 1 id, name, address FROM #S) AS S
ON #T.id = S.id AND #T.Name = S.Name
WHEN NOT MATCHED THEN
    INSERT VALUES(S.id,S.Name, S.Address)
WHEN MATCHED THEN
    UPDATE SET Address = S.Address;

Do you want to set the Address to a random element of the values from the source table? Use a TOP 1 and ORDER BY NEWID() clause in your subselect:

MERGE #T 
USING (SELECT TOP 1 id, name, address FROM #S ORDER BY NEWID()) AS S
ON #T.id = S.id AND #T.Name = S.Name
WHEN NOT MATCHED THEN
    INSERT VALUES(S.id,S.Name, S.Address)
WHEN MATCHED THEN
    UPDATE SET Address = S.Address;

If you match four source rows to a single target row, you'll never get a useful result - you need to know what you really want.

Marc

What could be the workaround to avoid the MERGE issue i.e. The target of a MERGE statement cannot be a remote table?

If you are not able to change the remote DB structure, I would suggest to build the ClassId mapping table right in the target Class table:

drop table if exists #ClassIdMap;
create table #ClassIdMap (SourceClassId int, TargetClassId int);
declare @Prefix varchar(10) = 'MyClassId=';

insert into targetServer.targetDb.dbo.Classes
    ([Name])
select
    -- insert the source class id values with some unique prefix
    [Name] = concat(@Prefix, Id)
from 
    sourceServer.sourceDb.dbo.Classes;

-- then create the ClassId mapping table
-- getting the SourceClassId by from the target Name column 

insert #ClassIdMap (
    SourceClassId,
    TargetClassId)
select
    SourceClassId = replace([Name], @Prefix, ''),
    TargetClassId = Id
from
    targetServer.targetDb.dbo.Class
where
    [Name] like @Prefix + '%';

-- replace the source Ids with the Name values 

update target set
    [Name] = source.[Name]
from
    targetServer.targetDb.dbo.Class target
    inner join #ClassIdMap map on map.TargetClassId = target.Id
    inner join sourceServer.sourceDb.dbo.Classes source on source.Id = map.SourceClassId;

-- and use the ClassId mapping table 
-- to insert Students into correct classes

insert into targetServer.targetDb.dbo.Students (
    [Name]  ,
    ClassId )
select
    s.[Name],
    ClassId = map.TargetClassId
from
    sourceServer.sourceDb.dbo.Students s
    inner join #ClassIdMap map on map.SourceClassId = s.ClassId;

The problem or risk with this script is that it is not idempotent — being executed twice it creates the duplicates.
To eliminate this risk, it is necessary to somehow remember on the source side what has already been inserted.

Merge Statement Error debugging

You could add something like this to the beginning of your merge procedure:

if exists (
  select 1 
    from a 
    group by a.OnColumn 
    having count(*)>1
    )
  begin;
    insert into merge_err (OnColumn, OtherCol, rn, cnt)
      select 
          a.OnColumn
        , a.OtherCol
        , rn = row_number() over (
            partition by OnColumn
            order by OtherCol 
            )
        , cnt = count(*) over (
            partition by OnColumn
            )
        from a
    raiserror( 'Duplicates in source table a', 0, 1)
    return -1;
  end;

test setup: http://rextester.com/EFZ77700

create table a (OnColumn int, OtherCol varchar(16))
insert into a values 
    (1,'a')
  , (1,'b')
  , (2,'c')

create table b (OnColumn int primary key, OtherCol varchar(16))
insert into b values 
    (1,'a')
  , (2,'c')

create table merge_err (
    id int not null identity(1,1) primary key clustered
  , OnColumn int
  , OtherCol varchar(16)
  , rn int
  , cnt int
  , ErrorDate datetime2(7) not null default sysutcdatetime()
  );

go

dummy procedure:

create procedure dbo.Merge_A_into_B as 
begin
set nocount, xact_abort on;
if exists (
  select 1 
    from a 
    group by a.OnColumn 
    having count(*)>1
    )
  begin;
    insert into merge_err (OnColumn, OtherCol, rn, cnt)
      select 
          a.OnColumn
        , a.OtherCol
        , rn = row_number() over (
            partition by OnColumn
            order by OtherCol 
            )
        , cnt = count(*) over (
            partition by OnColumn
            )
        from a
    raiserror( 'Duplicates in source table a', 0, 1)
    return -1;
  end;

/*
merge into b 
  using a 
  on b.OnColumn = a.OnColumn
  ...
--*/
end;
go

execute test proc and check error table:

exec dbo.Merge_A_into_B

select * 
from merge_err
where cnt > 1

results:

+----+----------+----------+----+-----+---------------------+
| id | OnColumn | OtherCol | rn | cnt |      ErrorDate      |
+----+----------+----------+----+-----+---------------------+
|  1 |        1 | a        |  1 |   2 | 05.02.2017 17:22:39 |
|  2 |        1 | b        |  2 |   2 | 05.02.2017 17:22:39 |
+----+----------+----------+----+-----+---------------------+

IF EXISTS and MERGE Statement

Seems you have duplicate rows in your target table which are loaded from your previous runs.

Note: Matching in a Merge does not consider the rows inserted (even duplicate) while running the Merge itself.

Below is my repro example with a sample data:

Table1: Initial data

Table2: Taget table

Merge Statement:

MERGE tb2 AS Target
USING tb1   AS Source
 ON Source.firstname = Target.firstname and 
    Source.lastname = Target.lastname 

-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (firstname, lastname, updated_date) 
VALUES (Source.firstname, Source.lastname, source.updated_date)

-- For Updates
WHEN MATCHED THEN UPDATE SET
   Target.updated_date      = Source.updated_date

-- For Deletes
WHEN NOT MATCHED BY Source THEN
DELETE;

When Merge is executed, it inserts all data without any errors.

New data in tb1:

When I run the Merge statement, it gives me the same error as yours.

As a workaround using one of the below options,

1. Add additional conditions if possible in the ON clause to uniquely identify the data.

2. Remove the duplicates from the source and merge the data into tb2 as below.

    --temp table
    drop table if exists #tb1;
   
    select *  into #tb1 from (
        select *, row_number() over(partition by firstname, lastname order by firstname, lastname, updated_date desc) as rn from tb1) a
    where rn = 1 
   
    MERGE tb2 AS Target
    USING #tb1 AS Source
     ON Source.firstname = Target.firstname and 
        Source.lastname = Target.lastname 
   
    -- For Inserts
    WHEN NOT MATCHED BY Target THEN
    INSERT (firstname, lastname, updated_date) 
      VALUES (Source.firstname, Source.lastname, source.updated_date)
   
    -- For Updates
    WHEN MATCHED THEN UPDATE SET
       Target.updated_date     = Source.updated_date
   
    -- For Deletes
    WHEN NOT MATCHED BY Source THEN
      DELETE;
   

Data merged into tb2 successfully.

SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE

You are joining the tables on ON source.MappingId = target.MappingId.

In your data sample, there are more than 1 row with same MappingId = 185761.
So here you got:

A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.

You need to specify some unique column combination to join the source and the target tables.

---

Related Topics