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
51 UPDATE SET
52 d.ItemNumber = o.ItemNumber,
Regards
Leo
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
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
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.
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.
