The Power of Merge

Question

Post reply

The Power of Merge

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

Just tried this and it works:

ON

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

WHEN

MATCHED THEN

UPDATE SET

d

.Qty = o.Qty,

d

.Rate = o.Rate,

d

.Deleted = 0

WHEN

NOT MATCHED THEN

INSERT (OrderNumber, ItemNumber, Qty, Rate)

VALUES (o.OrderNumber, o.ItemNumber, o.Qty, o.Rate)

WHEN

SOURCE NOT MATCHED THEN

UPDATE SET d.Deleted = 1

.

Wayne West SSC-Insane Points: 22586 More actions · Answer 2

Merge is part of the ISO SQL standard, the MS implementation always seems to lag a generation or so behind ISO.

There's nothing saying we have to use it, like so many other things it's just a tool that's available to us. It will be interesting to see some performance benchmarks, even if they're less than formal.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

J.Wilson Grasshopper Points: 14 More actions · Answer 3

Hi Jacob

What version sqlserver 2005 I must use. Why version .3042 it generates syntax error...with the commando MERGE.

thank you.

Wayne West SSC-Insane Points: 22586 More actions · Answer 4

It's in the 2008 beta, not in 2005.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Ian Yates SSCoach Points: 19738 More actions · Answer 5

Much desired new feature - should make the CRUD procedures a bit easier

I like the idea floated about flagging records as deleted rather than actually deleting them. I haven't experimented with 2008 yet (and a few of our customers are still running 2000 so I cannot even take advantage of this for a while!) but I think there will be many creative uses for the command as you needn't necessarily insert a record when not matched, just as you needn't necessarily delete.

Presumably only a single command can be executed - no begin/end blocks? Also, I would assume that the command has to be set based rather than, for example, a stored procedure execution (which would make the command cursor-based and ugly).

I would think the merge command allows the query optimiser to have some smarts such as avoiding multiple table/index scans that may occur if you do the

IF EXISTS... UPDATE... ELSE INSERT... (although the optimiser may already recognise this - I haven't checked lately).

jacob sebastian SSChampion Points: 11822 More actions · Answer 6

The examples were created with SQL Server 2008. SQL Server 2005 does not support MERGE. It is available only with SQL Server 2008

You can download SQL server 2008 (KATMAI) at https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

.

steve dassin SSCrazy Points: 2396 More actions · Answer 7

Jacob, Wayne, Charles and other interested parties,

There is the sql standard and then there is the 'relational' approach. I urge you to compare S2008 MATCH with the ideas presented here:

http://beyondsql.blogspot.com/2007/06/dataphor-intelligent-views.html

We should be talking about intelligient operations on 'views'. And views are something sql has...well dumbed down

MATCH, conceptually, has much in common with the S2008 idea of a table parameter. I'm trying to show these concepts in a mature form now, as opposed to the MS siblings of pre-adolescence

http://www.beyondsql.blogspot.com

I hope to be presenting at the Phoenix SQL Server User Group soon. I will try to make things lively and informative

Jake SSC Enthusiast Points: 156 More actions · Answer 8

Has anyone compared the performance of a MERGE statement with the "old" standard of UPDATE and INSERT? Specifically with regards to data warehouse loading.

Thanks,

jake

Rob Farley SSCrazy Points: 2738 More actions · Answer 9

Yes, it's faster. It doesn't need to loop through the data multiple times to find out which rows exist and therefore need updating, and which ones don't and therefore need inserting.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

malincog Newbie Points: 7 More actions · Answer 10

How can I accomplish the same result albeit slower in SQL Server 2005 - not using MERGE?

jacob sebastian SSChampion Points: 11822 More actions · Answer 11

In SQL Server 2005, you would need three separate DELETE, UPDATE and INSERT statements. You can find an example (look at the last example) here: http://www.sqlservercentral.com/columnists/jSebastian/2912.asp

.

rajeev_2004_3 Grasshopper Points: 16 More actions · Answer 12

Hi Sebastian,

On going thru your code, i could notice that you are delete the records by ...WHEN SOURCE NOT MATCHED THEN DELETE;

i suppose that it would delete all other records which doesn't meet the on clause join condition of the merge, like orderdetails of other orders ..........

Pls. clarify me...

adhiman SSC Veteran Points: 282 More actions · Answer 13

I was working with SQL 2008 R2 and found that to use delete when there is no match, the syntax got changed.

Instead of

"WHEN SOURCE NOT MATCHED THEN

DELETE;"

It is now

"WHEN NOT MATCHED BY SOURCE

THEN DELETE;"

Hope you guys have noticed. But Just an FYI