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 123»»»

The Power of Merge Expand / Collapse
Author
Message
Posted Tuesday, July 17, 2007 11:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3122.asp

.
Post #382489
Posted Monday, September 10, 2007 9:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 11:06 AM
Points: 164, Visits: 361
But why use XML to pass data in, when SQL2008 gives you Table-Value Parameters?

(I do love Merge - I spoke on it last month at the UG I run, and I'll be presenting on it in the next few months at other user-groups and at a code-camp too).



Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
Post #397941
Posted Monday, September 10, 2007 9:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 26, 2009 7:03 PM
Points: 13, Visits: 20

Surely the set d.ItemNumber = o.ItemNumber isn't needed as the "join" is on OrderNumber and ItemNumber:

MERGE OrderDetails AS d

   48 USING OrderInfo AS o

   49 ON (d.OrderNumber = o.OrderNumber AND d.ItemNumber = o.ItemNumber)

WHEN MATCHED THEN

   51     UPDATE SET

   52         d.ItemNumber = o.ItemNumber,

 

Regards

 

Leo

Post #397942
Posted Monday, September 10, 2007 9:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 11:06 AM
Points: 164, Visits: 361
Yes Leo, that's correct.



Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
Post #397947
Posted Tuesday, September 11, 2007 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 15, 2009 5:58 AM
Points: 2, Visits: 7

Hi,

Effectively, it seems really easy to use it. But what about performances? Is there any difference between using MERGE and using 'old' code ?

And what about MERGE and global SQL specifications? Is there a chance to have this keyword appears on the next sql language norm?

Anyway, thanks for this article. I'm impatient to install this new version to play with it

Have a nice day

 

PS: ALL WITH ME BEHIND FRENCH RUGBY TEAM (They need it :blush

Post #397966
Posted Tuesday, September 11, 2007 1:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

Hi Rob,

The focus of this article was to present the MERGE keyword and hence I started from where we stopped in a previous article.

There is another article scheduled, which introduces the table-value parameter and will be out in the next few days.

thanks

Jacob



.
Post #397971
Posted Tuesday, September 11, 2007 1:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Agreed with you, Leo.

.
Post #397972
Posted Tuesday, September 11, 2007 7:23 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:01 AM
Points: 6,705, Visits: 1,680

Im familiar with the scenario, but Im not sure I'm sold yet that this is substantially better - one more syntax with quirks you have to learn! Performance could be a key point, I look forward to seeing how it performs.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #398094
Posted Tuesday, September 11, 2007 7:56 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
Merge has been present in DB2 for years, it is a good thing that MS is catching up. Personally I find it handy for cetain things but I am afraid that this can be used on situations where a better application design is the key.

Thanks for the article. Simple and to the point.




* Noel
Post #398112
Posted Tuesday, September 11, 2007 8:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 06, 2014 12:59 PM
Points: 801, Visits: 1,962

What about REPLACE INTO?  That would be easier on the coders.  The way it's supposed to work is that if the primary key does not exists the row gets inserted.  If the row does exist it gets replaced.  There was some talk that rows affected would come ack as 1 if the insert happened and 2 if the row was replaced.  The thinking being that the replace was like a delete and an insert.

If I'm going to have to code procedures then I'll just code the procedures.  There is only one possible benefit from doing things the MERGE way.  I can update an existing order in my order table but new orders could get put into a new_order table.  Why you would want to do that I don't know.

I was jumping up and down happy when I read the article headline and then so dissapointed when I saw the syntax.



ATB

Charles Kincaid

Post #398140
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse