CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresmergesql-server-2012
Mutai
Mutai

Reputation: 125

Merge Statement Error debugging

I have a situation where I have a store procedure that contains a merge statement. The procedure grabs data from table A and uses the merge statement to update certain records in table B. The procedure works fine but occasionally, there are instances where there are duplicate records in table A. The store procedure is in a package with error notification set up and the package runs in a job and it gives the error below. Are there any ways that we can debug this? Like some where in the store procedure say if it gives an error then insert the source data into a table? Any input is appreciated.

Thanks

Error :

failed with the following error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Upvotes: 2

Views: 1464

Answers (1)

SqlZim
SqlZim

Reputation: 38023

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 |
+----+----------+----------+----+-----+---------------------+

Upvotes: 2

Related Questions