THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on SQLperformance.com

An Interesting MERGE Bug

Published Wednesday, August 4, 2010 7:30 AM by Paul White
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

ALZDBA said:

Thanks Paul.

Very, very nice plan and bug analysis.

August 4, 2010 5:36 AM
 

Paul White said:

You're very welcome :)

August 4, 2010 2:16 PM
 

JOO said:

Thanks ~

April 7, 2014 5:15 AM
 

Praveen said:

Hi Paul,

Very good analysis.

I tried re-producing this error in sql server 2008r2 with different kind of scenario and it gave me different results. Please go over it once if you have some time.

DECLARE @Target

TABLE   (

       Id int NOT NULL IDENTITY(1,1),

       row_id  INTEGER NOT NULL PRIMARY KEY,

       value   INTEGER NOT NULL

       );

DECLARE @Delta

TABLE   (

       row_id  INTEGER NOT NULL PRIMARY KEY,

       delta   INTEGER NOT NULL

       );

DECLARE @OTP

TABLE (ID int,

      Action varchar(10),

  RowId int,

  OldValue int,

  NewValue int

      )

-- Existing records (1 & 3)

INSERT  @Target

       (row_id, value)

VALUES  (1, 1),

       (3, 3);

-- Change table:

-- Updates rows 1 & 3

-- Inserts rows 2 & 4

INSERT  @Delta

       (row_id, delta)

VALUES  (1, 10),

       (3, 30),

(2, 20),

       (4, 40);

MERGE   @Target T

USING   @Delta D

       ON  D.row_id = T.row_id

WHEN    MATCHED THEN

       UPDATE SET T.value += D.delta

WHEN    NOT MATCHED BY TARGET THEN

       INSERT VALUES (D.row_id, D.delta)

OUTPUT  INSERTED.ID,

       $action,

D.row_id,

       DELETED.value AS old_value,

       INSERTED.value AS new_value INTO @OTP;

SELECT * FROM @OTP

April 7, 2015 2:54 PM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement