Using OUTPUT To Get Change Counts

Manoj Pandey shows how to use the OUTPUT clause to determine the number of records inserted, updated, or deleted after a DML statement:

–> Question:

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE separately and store it in a variable so I can get it in the application side?

Thanks !
–> My Answer:

You need to use OUTPUT clause with MERGE statement

Click through for a code sample.  The OUTPUT clause also works for non-MERGE statements like INSERT, UPDATE, and DELETE, though the “get changes by type” problem is really limited to the MERGE statement.

Related Posts

Identity Inserts: One Table at a Time

Bert Wagner shows that you can only insert with IDENTITY_INSERT = ON for one table at a time: Ok, simple enough to fix: we just need to do what the error message says and SET IDENTITY_INSERT ON for both tables: SET IDENTITY_INSERT dbo.User_DEV ON; SET IDENTITY_INSERT dbo.StupidQuestions_DEV ON; And… it still didn’t work: IDENTITY_INSERT is […]

Read More

Using APPLY to Reduce Function Calls

Erik Darling shows a clever use of the APPLY operator: A while back, Jonathan Kehayias blogged about a way to speed up UDFs that might see NULL input. Which is great, if your functions see NULL inputs. But what if… What if they don’t? And what if they’re in your WHERE clause? And what if they’re in […]

Read More

Categories

March 2017
MTWTFSS
« Feb Apr »
 12345
6789101112
13141516171819
20212223242526
2728293031