Click here to monitor SSC
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36150 Visits: 18751
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 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
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: 1272 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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2955 Visits: 831
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.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1153 Visits: 768
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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 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
Say Hey Kid
Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)

Group: General Forum Members
Points: 662 Visits: 267
Good question!
Bhavesh_Patel
Bhavesh_Patel
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

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



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10714 Visits: 12017
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