May 8, 2012 at 10:17 am
I have a lot of Stored Procedures that use a Merge statement.
I need to capture aggregate counts (Insert, Update & Delete) for each table.
What is the best way to do this how much of a performance hit can I anticipate?
http://stackoverflow.com/questions/1268491/sql-server-2008-merge-best-way-to-get-counts
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 8, 2012 at 10:24 am
Far as I know, the only way to do it is using the OUTPUT clause as per your link.
You'll need to aggregate the data yourself to screen or a holding table.
If you had individual Insert/Update/Delete statements, you could use @@ROWCOUNT, and no need for aggregating.
Or maybe something funky using triggers.
But I'd prefer to keep the MERGE statements!
May 14, 2012 at 2:03 am
I saw an article that may help with this: http://www.sqlskills.com/BLOGS/BOBB/post/Using-the-OUTPUT-clause-results-and-INSERT-SELECT.aspx
Adapting the code there to your problem (alas, without being able to test in my SQL 2008 playground):
CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0));
CREATE TABLE AuditChanges (Action varchar(6), Stock VARCHAR(6), Qty INT);
GO
INSERT Stock VALUES('MSFT', 10), ('BOEING', 5);
INSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
GO
SELECT action, COUNT(action) As Counts FROM
(
MERGE Stock S
USING Trades T
ON S.Stock = T.Stock
WHEN MATCHED AND (Qty + T.Delta = 0) THEN
DELETE
WHEN MATCHED THEN
UPDATE SET Qty += T.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(Stock, T.Delta)
OUTPUT $action, T.Stock, inserted.Qty
) tab (action, stock, qty);
GROUP BY action
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply