Discussion on:

Using SQL Server 2008's MERGE statement

This is a great idea. It can reduce the "CU" of my CRUD stored procedures to have one for both create and update.

When doing the "WHEN MATCHED", can you do a begin/end block there so that you can capture the @@IDENTITY value?

You could do that, but it might be a better idea to capture the results for an identity column into an output table using SQL 2005s OUTPUT clause.

For reference purposes, where is the output statement located within this call? Please provide an example, Thanks.

I believe it is something like this, but will have to confirm later. I don't have a 2008 machine with me right now.

MERGE SalesArchive AS SA
USING (
SELECT
CustomerID,
LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),
TotalSalesAmount = SUM(SaleAmount),
TotalSalesCount = COUNT(*)
FROM SalesFeed
GROUP BY CustomerID
) AS SalesFeedCTE (CustomerID, LoadDate, TotalSalesAmount, TotalSalesCount)
ON
(
SA.CustomerID = SalesFeedCTE.CustomerID AND SA.SalesDate = SalesFeedCTE.LoadDate
)
WHEN NOT MATCHED THEN
INSERT (CustomerID, SalesDate, TotalSalesAmount, TotalSalesCount, CreationDate, UpdatedDate)
VALUES( SalesFeedCTE.CustomerID, SalesFeedCTE.LoadDate, SalesFeedCTE.TotalSalesAmount, SalesFeedCTE.TotalSalesCount, GETDATE(), GETDATE())
WHEN MATCHED THEN
UPDATE
SET SA.TotalSalesAmount = SA.TotalSalesAmount + SalesFeedCTE.TotalSalesAmount,
SA.TotalSalesCount = SA.TotalSalesCount + SalesFeedCTE.TotalSalesCount,
SA.UpdatedDate = GETDATE()
OUTPUT INSERTED.SalesAmount, DELETED.SalesAmount;

My tables don't have any ID columns, but you would access the ID column the same way in the output clause. You can enter the output into a table as well. For an example of that, see here:http://articles.techrepublic.com.com/5100-9592_11-6074046.html

This is an interesting concept. In around about way we've achieved this in SQL 2000. Basically, we check the table with a IF NOT EXISTS statement. If nothing exists for the criteria we insert the base columns that are required.

Then regardless if it found or if it was inserted, we update the rest of the table either by the passed in PK or the new Identity created for that row.

I don't quite understand how a delete would work in this. I'm kind of guessing that in stead of a update you have a delete statement.

Is there a way to include all three statements? It would be nice to have one stored procedure for all statements.

This statement has an INSERT and an UPDATE integrated - sure its consolidated but it could all be done easily before...

UPDATE a SET
a.col1 = b.col1
,a.col2 = b.col2
FROM tableone a
JOIN tabletwo b ON a.foreign_id = b.[id]
WHERE {limit clauses}

INSERT INTO tableone
SELECT col1, col2 FROM tabletwo
WHERE [id] NOT IN (SELECT foreign_id FROM tableone)

You can still integrate aggregate functions here as well. Not sure I see the advantage of MERGE...

You have written two different statements, that read the tables two different times.

from the above poster's example and your reply, the merge statement would be good for when you are updating from large tables since the the table is scanned only once; however, for smaller tables it might be better to avoid the merge statement for clarity. Would that be correct to assume?

While I agree the table may be scanned once, the MERGE statement still invokes both an INSERT and UPDATE. The optimizer may build a special plan to leverage the lookup, but in the end the INSERT and UPDATE are the significant operations in terms of query weight.

I think the performance advantages would be negligible, but I haven't seen any tests yet. It would be good for all to see an analysis of various types of applications of MERGE.