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

Comparing Ranking Functions

Doug Kline compares three window functions:  RANK, DENSE_RANK, and ROW_NUMBER: — so let’s say that we’ve created a contest — places in the contest (top place, 2nd place, etc.) — will be determined by the test score — in other words, we’re not so concerned with the raw score — but rather, we’re interested in […]

Read More

Updating Data In Common Table Expressions

Kenneth Fisher shows that you can directly update a table referenced in a common table expression: CTEs are cool things. You can essentially create one or more in-line view(s) within your query. One thing that isn’t overly well known is that you can actually update the data within the CTE. No, I don’t mean using using the UPDATE statement […]

Read More


March 2017
« Feb Apr »