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, September 11, 2007 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 10:14 AM
Points: 18, Visits: 167

Great example, and thanks for the info on the MERGE functionality.  A question about usage...

With this syntax:

WHEN SOURCE NOT MATCHED THEN

        DELETE

Can I put other things in there instead of "DELETE"?  I don't physically delete from my databases - we use a logical delete using an InactiveDate field.  Is there a way to set that InactiveDate field for those records that are in the source but not matched with any data in the XML (and therefore need to be "deleted")?

Thanks.

Post #398142
Posted Tuesday, September 11, 2007 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 13, 2009 5:31 PM
Points: 8, Visits: 34
What triggers get fired with Merge ? If it's more then one trigger, is there a fixed order or can they be fired in a random order ?

Thanks,
Don
Post #398164
Posted Tuesday, September 11, 2007 9:39 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

Here is an example:

WHEN SOURCE NOT MATCHED THEN

    --DELETE

    UPDATE SET

    d.Deleted = 1

The above code updates the status of the column "deleted" to 1, instead of physically deleting the record.

If you are doing this,  you need another change at the JOIN condition, to skip the deleted records.

USING OrderInfo AS o

ON (d.OrderNumber = o.OrderNumber AND d.ItemNumber = o.ItemNumber AND d.Deleted = 0)

 



.
Post #398172
Posted Tuesday, September 11, 2007 9:46 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: Today @ 4:29 PM
Points: 820, Visits: 2,026
Cool.  Then how do you undeleted a deleted record?

ATB

Charles Kincaid

Post #398177
Posted Tuesday, September 11, 2007 9:50 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

From my testing, i found that the triggers are always fired in the following order.

1. insert

2. update

3. delete

I am not sure if we can always assume this order.

 



.
Post #398179
Posted Tuesday, September 11, 2007 9:54 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

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



.
Post #398181
Posted Tuesday, September 11, 2007 11:59 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: Today @ 10:22 AM
Points: 914, Visits: 2,051
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.


-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Post #398216
Posted Tuesday, September 11, 2007 4:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 22, 2011 9:36 PM
Points: 8, Visits: 13

Hi Jacob

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

 

thank you.

Post #398289
Posted Tuesday, September 11, 2007 4:31 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 914, Visits: 2,051
It's in the 2008 beta, not in 2005.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Post #398290
Posted Wednesday, September 12, 2007 5:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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).




Post #398767
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse