Need help with merge

Question

Need help with merge

kapil_kk

SSC-Insane

Points: 21316
More actions
January 29, 2014 at 11:36 pm

#303843

Hi all,
While working with MERGE I fond an issue. Here is the below code to recreate the scenario:
create table #temp
(
Id int identity(1,1),
Number int,
LoadDate datetime default(getdate()),
constraint unq_temp unique
(
Number
)
)
;with table1(no1) as
(
select 1
union all
select 2
union all select 3
union all
select 1
),
table2(no2) as
(
select 1 union all select 1
)
MERGE #temp T
Using table1 T1 ON T.number = T1.no1
WHEN matched THEN
UPDATE
SET T.number = T1.no1
WHEN NOT MATCHED THEN
INSERT
(number)
VALUES
(T1.no1);
--truncate table #temp
select * from #temp
drop table #temp
I want to know that when value 1 is inserted into #temp table then second time it should go to UPDATE part rather than INSERT.
It gives me error that:
Msg 2627, Level 14, State 1, Line 11
Violation of UNIQUE KEY constraint 'unq_temp'. Cannot insert duplicate key in object 'dbo.#temp'. The duplicate key value is (1).
The statement has been terminated.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

It's by design, Kapil. There are two steps to the update/insert - a read to determine the rows which will be affected (by the join to source) and then the update/insert. The read remains unchanged throughout as a static reference - in other words, a snapshot. The purpose of this is to ensure that changes occur in a predictable manner. It's called Halloween Protection and Craig Freedman describes it far better than I can here.

It's up to you to shape your source data appropriately.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

ChrisM@Work (1/30/2014)
It's by design, Kapil. There are two steps to the update/insert - a read to determine the rows which will be affected (by the join to source) and then the update/insert. The read remains unchanged throughout as a static reference - in other words, a snapshot. The purpose of this is to ensure that changes occur in a predictable manner. It's called Halloween Protection and Craig Freedman describes it far better than I can here.
It's up to you to shape your source data appropriately.

Is there any alternate way to achieve this other than using IF EXISTS...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Use a traditional upsert.

What you're trying to do in your original MERGE is to update rows which don't exist.

Perform your inserts first from a deduped source set - that is, deduped on whatever you are using to determine uniqueness. The remaining rows of your source set are all updates. You could use ROW_NUMBER() to distinguish between the two. EXISTS is the first option I'd experiment with for determining which of the deduped rows to insert into the target.

Edit: clarity

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

Yes I am trying to insert rows which does not exists and update rows if already exists...

Can you please convert my posted query into the solution that you are suggesting so that I am clear with that and can implement here...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Chirs, I can use IF EXISTS but as it will perform row by row so when there is bulk data arounds 5-10 lakhs that my procedure will take long time to execute and it will hit performance thats why I choose MERGE....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

MERGE #temp T

Is the table #temp always empty before you start the upsert?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

ChrisM@Work (1/30/2014)
MERGE #temp T
Is the table #temp always empty before you start the upsert?

No it will not empty..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Let's say you have a "dupe pair" of rows in your source table - dupes on the basis of a unique key on one or more columns.

Does it matter in which order they are applied to the target table? Think about this for a moment. Assume there's a column called [OrderQuantity] and in one of the rows it has the value 5 and in the other it has the value 3. One of these two rows will be inserted, the other will be subsequently used for an update.

If you choose the row with [OrderQuantity] = 5 for the INSERT, then the update row will change [OrderQuantity] to 3.

If you choose the row with [OrderQuantity] = 3 for the INSERT, then the update row will change [OrderQuantity] to 5.

You have to determine the order in which the two source rows will affect the target table.

If there can be more than two source rows then you've lost control, because you can only update a target row once in one statement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

ChrisM@Work (1/30/2014)
Let's say you have a "dupe pair" of rows in your source table - dupes on the basis of a unique key on one or more columns.
Does it matter in which order they are applied to the target table? Think about this for a moment. Assume there's a column called [OrderQuantity] and in one of the rows it has the value 5 and in the other it has the value 3. One of these two rows will be inserted, the other will be subsequently used for an update.
If you choose the row with [OrderQuantity] = 5 for the INSERT, then the update row will change [OrderQuantity] to 3.
If you choose the row with [OrderQuantity] = 3 for the INSERT, then the update row will change [OrderQuantity] to 5.
You have to determine the order in which the two source rows will affect the target table.
If there can be more than two source rows then you've lost control, because you can only update a target row once in one statement.

Yes, I will decide the order on basis of TimeStamp value....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Can you post up some sample data, Kapil? Showing multiple rows per key? Obfuscate if necessary, but it really would be very useful to see it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

ChrisM@Work (1/30/2014)
Can you post up some sample data, Kapil? Showing multiple rows per key? Obfuscate if necessary, but it really would be very useful to see it.

I already posted a scenario with some dummy data in my first post....

Isn't that worked for you?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/