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


SQL 2008 T-SQL


SQL 2008 T-SQL

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151194 Visits: 19455
I missed that subtlety with 1 and 2. I've changed the answers to clean things up and will award back points.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Longy
Longy
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1500 Visits: 944
I read the first answer, knew it was right and ignored the rest. I don't see how doing nothing is an action



bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16331 Visits: 25280
Composed before I read Steve Jones post ....

Sorry about that Steve I should have hit the refresh before hitting post.


Scott Coleman
How on earth can you claim "no changes" is an action that was executed?

Cliff Jones
Yes, please explain the difference between answers 1 and 2 because it is very subtle


The action that was executed by the merge statement was the comparison between all the data in the table depts and all the data in table delta to determine if additional further action was required.

The objective of the QOD as I understand it is to get people thinking and learning. And the subtle actions of the merge is that all the rows in both tables are examined.

Scott Coleman
I'm really getting tired of questions that play word games


Scott Coleman - how about you submitting a QOD. It is quite a challenge.

Edited at 11:20 AM

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
paul.goldstraw
paul.goldstraw
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1510 Visits: 1765
I'm not trying to be difficult here but I have to disagree with you bitbucket.

If I write an update statement i get a response along the lines of

23 row(s) affected



Your correct answer implies that what we should expect is

23 row(s) affected and 34 row(s) unaffected



which isn't the case. If a row is untouched, as row 1 is, the fact it was untouched is implicit and needs no explanation as such. I appreciate it was an error that has now been corrected but trying to defend it by suggesting we missed a subtlty of the question rather than admitting that it was wrong is missing the objective of the QotW as much as you suggest we are.

Paul
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3902 Visits: 859
Excellent question.

First because it demonstrates the MERGE,
Second because it illustrates the $action, and
Last because the format for the output makes the answer particularly clear.

Specifically:
INSERT 6 Production Jones NULL NULL NULL
UPDATE 2 Sales Erickson 2 Sales Byham
DELETE NULL NULL NULL 5 Manufacturing Brewer

Nice one!

Jamie
dave.farmer
dave.farmer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1341 Visits: 795
Jamie Longstreet (8/21/2009)
Excellent question.

First because it demonstrates the MERGE,
Second because it illustrates the $action,

Nice one!


Actually, I was going to say much the same but got a bit sidetracked by the answer confusion... I didn't know about MERGE before but thanks to this QotD it's clear how it works and it will be most helpful in solving a problem I have at work without resorting to extra and less efficient processing.

Cheers!
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16331 Visits: 25280

Jamie Longstreet (8/21/2009)
--------------------------------------------------------------------------------
Excellent question.

First because it demonstrates the MERGE,
Second because it illustrates the $action,

Nice one!


dave.farmer

Actually, I was going to say much the same but got a bit sidetracked by the answer confusion... I didn't know about MERGE before but thanks to this QotD it's clear how it works and it will be most helpful in solving a problem I have at work without resorting to extra and less efficient processing.Cheers!


Thank you for your responses, it is satisfying to recieve affirmation that the QOD has achieved at least a portion of its objective.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
VM-723206
VM-723206
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1050 Visits: 267
Good question!
Bhavesh_Patel
Bhavesh_Patel
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 297
I was lucky Hehe I got it right!



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26513 Visits: 12506
A nice question. I was lucky enough to see it after the wording was changed to reduce the number of correct answers to one.

But I do feel that this question illustrates how nasty MERGE is - we were better off without it, at least from the point of view of code clarity.

Tom

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