SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with merge


Need help with merge

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7657 Visits: 2776
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/
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65836 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7657 Visits: 2776
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
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65836 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7657 Visits: 2776
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
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7657 Visits: 2776
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
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65836 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7657 Visits: 2776
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
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65836 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7657 Visits: 2776
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search