<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Greg Nicol
»
Merge error handling
14 posts, Page 2 of 2
««
1
2
Merge error handling
Rate Topic
Display Mode
Topic Options
Author
Message
Treehouse
Treehouse
Posted Thursday, November 04, 2010 6:33 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 12, 2013 5:50 AM
Points: 33,
Visits: 234
This is good stuff... exactly what I need to troubleshoot a strange unique key violation with a MERGE.
Question though- why is the IDENTITY column of the type an INT, while the @single_update_id param to the proc is a BIGINT? I am guess the two datatypes should match, depending on the size needed for the particular application.
Thanks!
Post #1015807
emailgregn
emailgregn
Posted Thursday, November 04, 2010 6:39 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 07, 2012 11:11 AM
Points: 7,
Visits: 104
Matthew-399425 (11/4/2010)
Question though- why is the IDENTITY column of the type an INT, while the @single_update_id param to the proc is a BIGINT? I am guess the two datatypes should match, depending on the size needed for the particular application.
You're right, the datatypes should match and sizing depends on the application.
Post #1015814
emailgregn
emailgregn
Posted Thursday, November 04, 2010 6:53 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 07, 2012 11:11 AM
Points: 7,
Visits: 104
Something else I recently learned that I left out of the article for clarity is about duplicate keys in the source data.
MERGE gets broken horribly if there are duplicate alternate keys in the <table_source> i.e. rows with the same customer_id in the @CustomerUpdates UDDT.
A validation step to detect / remove any duplicates before the merge statement can help catch otherwise hard to detect errors.
HTH
Post #1015828
jpratt-797544
jpratt-797544
Posted Tuesday, November 09, 2010 8:54 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, May 03, 2013 12:27 PM
Points: 21,
Visits: 42
This is a great post and a great demo of recursion capabilities in T-SQL/SPs. My current issue is the duplicate key/ID in the source data. What I am trying to do is get the recursive call to just skip to the next highest ID, but the MERGE OUTPUT apparently does not contain anything at all if an error occurs so I can't get the MAX(ID) from the output. In addition, I would rather not have to keep creating UDDTs and passing TVPs for every MERGE I need to do.
*sigh* It seems like another bright idea that Microsoft didn't implement very well (see postings online about duplicate key handling and other features of other RDBMSes).
I need single row error handling and logging, so I am going back to trusty cursors that loop through the IDs and then do a MERGE on each record processed. FYI, cursors perform great when the cursor declare only processes indexed IDs. Then you do the work retrieving other values inside the cursor loop.
Post #1018002
« Prev Topic
|
Next Topic »
14 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
