SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 More fun with Katmai
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/30/2007 :  19:22:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Not only is new MERGE command able to do UPSERTS.
It can also do DELETE.
-- Prepare sample data 
DECLARE	@Base TABLE (ID INT, Data INT) 

INSERT  @Base 
SELECT  1, 1 UNION ALL 
SELECT  2, 1 

DECLARE @New TABLE (ID INT, Data INT) 

INSERT  @New 
SELECT  1, 0 UNION ALL 
SELECT  2, 1 UNION ALL 
SELECT  2, 1 UNION ALL 
SELECT  3, 1 

-- Before
SELECT	*
FROM	@Base

-- Do the magic
MERGE   @Base AS b 
USING   ( 
                SELECT          ID, 
                                SUM(Data) 
                FROM            @New 
                GROUP BY        ID 
        ) AS src (ID, Data) ON src.ID = b.ID 
WHEN    MATCHED AND src.Data = 0 
        THEN DELETE
WHEN    MATCHED 
        THEN UPDATE SET b.data = src.Data
WHEN    NOT MATCHED THEN 
        INSERT (ID, Data) VALUES (src.ID, src.Data); -- Last statement must end with a comma

-- After
SELECT	*
FROM	@Base



E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/30/2007 :  19:30:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Before
ID  Data
--  ----
1   1
2   1
After
ID  Data
--  ----
2   2
3   1


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 07/30/2007 19:31:20
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 07/30/2007 :  19:31:41  Show Profile  Visit dinakar's Homepage  Reply with Quote
Kool. Wish they added it to 2005 in SP2 or even SP3....

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/31/2007 :  02:21:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Good Test

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/31/2007 :  02:40:24  Show Profile  Reply with Quote
Is it just me, or is that spaghetti of code really better than a

INSERT ...
IF @@ROWCOUNT = 0 UPDATE ...

??

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/31/2007 :  02:53:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
When doing an UPSERT with DELETES, I do this order of events

1) DELETE
2) UPDATE
3) INSERT

Each and one of these steps requires a SCAN/SEEK. 3 in total. MERGE command is only one.
I will try to do some tests again this evening.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/31/2007 :  04:43:31  Show Profile  Visit spirit1's Homepage  Reply with Quote
while you're at it see what locks are being held
i've looked a little at theat aspect of the "old way" here:
http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/31/2007 :  04:55:51  Show Profile  Reply with Quote
To Sync. two tables I can see DELETE being useful, but in practice you have to do all the Deletes first in reverse-order of FKs, and then the Updates/Inserts in forward-order of FKs.

For a standard CRUD type SProc I don't see DELETE needing to be part of UPSERT ...

So for a

INSERT ...
IF @@ROWCOUNT = 0 UPDATE ...

its one SCAN/SEEK if the row is new, 2 otherwise. As NR has suggested before, put the UPDATE first if that's the more common occurrence, then you get 2 SCAN/SEEK for a new row, and one for an existing row.

Maybe I'm getting old, but I think the code is easier to read [i.e. Maintenance is less error prone] with separate Insert/Update statements

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.24 seconds. Powered By: Snitz Forums 2000