More fun with Katmai - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

More fun with Katmai

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 19:22:27

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

30421 Posts

Posted - 2007-07-30 : 19:30:48

Before

ID  Data
--  ----
1   1
2   1

After

ID  Data
--  ----
2   2
3   1

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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-30 : 19:31:41

Kool. Wish they added it to 2005 in SP2 or even SP3....

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-31 : 02:21:24

Good Test

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-07-31 : 02:40:24

Is it just me, or is that spaghetti of code really better than a

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

??

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 02:53:27

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"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-31 : 04:43:31

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

Kristen
Test

22859 Posts

Posted - 2007-07-31 : 04:55:51

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources