Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need help with merge Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 11:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1536180
Posted Thursday, January 30, 2014 1:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 7,178, Visits: 13,630
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
Post #1536197
Posted Thursday, January 30, 2014 2:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1536200
Posted Thursday, January 30, 2014 2:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 7,178, Visits: 13,630
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
Post #1536207
Posted Thursday, January 30, 2014 2:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1536212
Posted Thursday, January 30, 2014 2:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1536214
Posted Thursday, January 30, 2014 2:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 7,178, Visits: 13,630
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
Post #1536219
Posted Thursday, January 30, 2014 3:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1536223
Posted Thursday, January 30, 2014 3:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 7,178, Visits: 13,630
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
Post #1536228
Posted Thursday, January 30, 2014 3:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1536231
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse