Get inserted or deleted records in SQL - OUTPUT Clause

In SQL, how we identify the last inserted or deleted records? We’ve used @@identity to get the last inserted records identity value. This will not be sufficient while insert lot of records in same time. To solve that, we have OUTPUT clause to get the inserted/deleted data information.

Especially this OUTPUT clause is very useful while using MERGE clause. Since this is the only way to get inserted/deleted records in TARGET table.

OUTPUT Clause:
Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Ref: http://technet.microsoft.com/en-us/library/ms177564.aspx

Syntax:
::=
{
    [ OUTPUT INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT ]
}
::=
{ | scalar_expression } [ [AS] column_alias_identifier ]
    [ ,...n ]

::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }

    | $action

For more understanding go to below link.
http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/

Comments

Post a Comment

Popular posts from this blog

BDD - Acceptance Test Driven Development

Image
Acceptance Test Driven Development What is BDD: BDD is an agile software development technique that encourages collaboration between developers, QA, and non-technical or business participants in a software project. It's more about business specifications than about tests. You write a specification for a story and verify whether the specs work as expected. The main features of BDD development are outlined below:   A testable story (it should be the smallest unit that fits in an iteration) The title should describe an activity The narrative should include a role, a feature, and a benefit The scenario title should say what's different The scenario should be described in terms of Givens, Events, and Outcomes The givens should define all of, and no more than, the required context The event should describe the feature   Advantage of BDD:   The first advantage is that this domain language is usable/understandable by domain experts as well. Given that you e...
Read more

Angular JS – Part 2

Angular Routing Services and Directives: Routing Services: $route - $routeProvider is used to map the URL with template + assigned controller. (specially for SPAs). Methods: when(), otherwise(redirectTo:), current, params, pathParams, reload(). Also we can use resolve() to decide before load template. $routeParams – Used to pass the parameters via URL $location –Use to enable html5 routing. Can get the URL informations Eg. absUrl, protocol, port, host, path, search, hash, url. Replace() method will not keep URL history. Directives: Uses of Directives: Create custom element, custom event, observe and react to changes Use eventApp.dirctive("directiveName",function(){ return {restrict: 'E', template: "<input>"}}); Can restrict the directive to how should use. E(element), C(class), A(attribute), M(comment). Instead of template HTML text, the URL can be used. Use" replace: true" to replace the element name to html i...
Read more

.Net Collections

The  System.Collections namespace contains interfaces and classes that define various collections of objects, such as lists, queues, bit arrays, hash tables and dictionaries. Array list: Implements the IList interface using an array whose size is dynamically increased as required. Dictionary Base: Provides the abstract (MustInherit in Visual Basic) base class for a strongly typed collection of key-and-value pairs. Dictionary is a Generic type. So we can get type safety with Dictionary. Hash table: Represents a collection of key-and-value pairs that are organized based on the hash code of the key. A key cannot be a null reference (Nothing in Visual Basic), but a value can be. Hashtable is not a Generic type. So we can’t get type safety with Hashtable. Hashtable is thread safe. Queue: Represents a first-in, first-out collection of objects. Queues are useful for storing messages in the order they were received for sequential processing. Stack: ...
Read more