Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - I noticed very long stored procedure in the production database

  • I noticed very long stored procedure in the production database. This database creating temporary tables, using insert and select statement. When I asked the purpose of the stored procedure, I realize they should have just used OUTPUT clause. What is the OUTPUT clause inside SQL Server?

    Posted on 01-10-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

14  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    7

    SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table.

    Syntax

    { [ OUTPUT { { DELETED | INSERTED | fromtablename } . { * | columnname } | scalarexpression } [ [AS] columnaliasidentifier ] [ ,...n ] INTO { @tablevariable | outputtable } [ ( columnlist ) ] ] [ OUTPUT { { DELETED | INSERTED | fromtablename } . { * | columnname } | scalarexpression } [ [AS] columnalias_identifier ] [ ,...n ] ] }

    @tablevariable Specifies a table variable that the returned rows are inserted into instead of being returned to the caller. @tablevariable must be declared before the INSERT, UPDATE, DELETE, or MERGE statement.

    If columnlist is not specified, the table variable must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns, which must be skipped. If columnlist is specified, any omitted columns must either allow null values or have default values assigned to them.

    For more information about table variables, see table (Transact-SQL).

    outputtable Specifies a table that the returned rows are inserted into instead of being returned to the caller. outputtable may be a temporary table.

    If columnlist is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If columnlist is specified, any omitted columns must either allow null values or have default values assigned to them.

    output_table cannot:

    Have enabled triggers defined on it. Participate on either side of a FOREIGN KEY constraint. Have CHECK constraints or enabled rules.

    column_list Is an optional list of column names on the target table of the INTO clause. It is analogous to the column list allowed in the INSERT statement.

    scalarexpression Is any combination of symbols and operators that evaluates to a single value. Aggregate functions are not permitted in scalarexpression. Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.

    columnaliasidentifier Is an alternative name used to reference the column name.

    DELETED Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed. DELETED cannot be used with the OUTPUT clause in the INSERT statement.

    INSERTED Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

    INSERTED cannot be used with the OUTPUT clause in the DELETE statement.

    fromtablename Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

    If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

    * Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table. OUTPUT DELETED.* in the following DELETE statement returns all columns deleted from the ShoppingCartItem table: Example: DELETE Sales.ShoppingCartItem OUTPUT DELETED.*;

    Example Using OUTPUT INTO with a simple INSERT statement

    USE AdventureWorks2008R2;

    GO

    DECLARE @MyTableVar table( NewScrapReasonID smallint, Name varchar(50), ModifiedDate datetime);

    INSERT Production.ScrapReason

    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
    
        INTO @MyTableVar
    

    VALUES (N'Operator error', GETDATE());

    --Display the result set of the table variable.

    SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

    --Display the result set of the table.

    SELECT ScrapReasonID, Name, ModifiedDate

    FROM Production.ScrapReason;

    GO

    Replied on Jan 10 2012 12:15AM  . 
    SQLShan
    691 · 0% · 48
  • Score
    8

    SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table.

    use tempdb;
    go
    create table itest ( i int identity not null primary key, j int not null unique );
    go
    create trigger insert_itest on itest after insert
    as
    begin
        insert into #new ( i, j )
        select i, j
          from inserted;
    end
    go
    create table #new ( i int not null, j int not null );
    insert into itest ( j )
    select o.id from sysobjects as o;
    -- Newly inserted rows and identity values:
    select * from #new;
    
    -- #new can be used now to insert into a related table:
    drop table #new, itest;
    go
    
    

    This code can be re-written in SQL Server 2005 using the OUTPUT clause like below:

    create table itest ( i int identity not null primary key, j int not null unique )
    create table #new ( i int not null, j int not null)
    
    insert into itest (j)
    output inserted.i, inserted.j into #new
    select o.object_id from sys.objects as o
    
    select * from #new
    drop table #new, itest;
    go
    

    The OUTPUT clause is not supported in the following statements:

    DML statements that reference local partitioned views, distributed partitioned views, or remote tables.

    INSERT statements that contain an EXECUTE statement.

    Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

    The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.

    A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

    Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.

    A column from a view or inline table-valued function when that column is defined by one of the following methods:

    A subquery.

    A user-defined function that performs user or system data access, or is assumed to perform such access.

    A computed column that contains a user-defined function that performs user or system data access in its definition.

    When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised. For more information, see MSSQLSERVER_4186.

    The OUTPUT clause supports the large object data types: nvarchar(max), varchar(max), varbinary(max), text, ntext, image, and xml. When you use the .WRITE clause in the UPDATE statement to modify an nvarchar(max), varchar(max), or varbinary(max) column, the full before and after images of the values are returned if they are referenced. The TEXTPTR( ) function cannot appear as part of an expression on a text, ntext, or image column in the OUTPUT clause.

    Permissions

    SELECT permissions are required on any columns retrieved through or used in .

    INSERT permissions are required on any tables specified in .

    Scenarios where we can use OUTPUT clause:

    A. Using OUTPUT INTO with a simple INSERT statement

    B. Using OUTPUT with a DELETE statement

    C. Using OUTPUT INTO with an UPDATE statement

    D. Using OUTPUT INTO to return an expression

    E. Using OUTPUT INTO with fromtablename in an UPDATE statement

    F. Using OUTPUT INTO with fromtablename in a DELETE statement

    G. Using OUTPUT INTO with a large object data type

    H. Using OUTPUT in an INSTEAD OF trigger

    I. Using OUTPUT INTO with identity and computed columns

    J. Using OUTPUT and OUTPUT INTO in a single statement

    K. Inserting data returned from an OUTPUT clause

    Replied on Jan 10 2012 12:21AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    7

    What is 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.

    You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

    Note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

                 Sometime we are using triggers to get old and new values or new values for the historical data to save in another table. So we can get inserted, updated and deleted data using triggers. 
    
    But one amazing feature "Output" clause. This clause we are mostly use with stored procedures to get output variables. Output clause also can be used with insert, update and delete statements.
    

    In SQL Server 2005 and 2008, you can add an OUTPUT clause to your data manipulation language (DML) statements. The clause returns a copy of the data that you’ve inserted into or deleted from your tables. You can return that data to a table variable, a temporary or permanent table, or to the processing application that’s calls the DML statement. You can then use that data for such purposes as archiving, confirmation messaging, or other application requirements.

    Both SQL Server 2005 and 2008 let you add an OUTPUT clause to INSERT, UPDATE, or DELETE statements. SQL Server 2008 also lets you add the clause to MERGE statements. In this article, I demonstrate how to include an OUTPUT clause in each one of these statements. I developed the examples used for the article on a local instance of SQL Server 2008. The examples also work in SQL Server 2005, except for those related to the MERGE statement.

    You can read this artical Click


    The OUTPUT clause is not supported in the following statements:

    1) A target with a FOREIGN KEY constraint, or referenced by a FOREIGN KEY constraint, Triggers on the target,a target participating in replication 
    
    2) DML statements that reference local partitioned views, distributed partitioned views, or remote tables.i.e A remote table, view, or common table expression as it’s target.  
    
    3) INSERT statements that contain an EXECUTE statement.
    
    4) Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
    
    5) The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
    
    6) A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
    
    7) Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access.User-defined functions are assumed to perform data access if they are not schema-bound
    

    Besides the above, be careful with the behaviour of @@ROWCOUNT. It will only return the number of rows affected by the outer INSERT statement.


    To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

                Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound. 
    
                A column from a view or inline table-valued function when that column is defined by one of the following methods:
    

    A subquery.

                A user-defined function that performs user or system data access, or is assumed to perform such access.
    
                A computed column that contains a user-defined function that performs user or system data access in its definition.
    

    In SQL Server 2008, one of the new T-SQL enhancements makes this pretty simple. One can now define a table expression based off a modification statement with an OUTPUT clause and then apply the filters on it.For Example:

    INSERT INTO dbo.PO_DTL (….)
    SELECT ….
    FROM (UPDATE dbo.INPT_PO_DTL
    SET Quantity *= 1.5
    OUTPUT
    inserted.PO_DTL_ID,
    deleted.Quantity AS Old_Qty,
    inserted.Quantity AS New_Qty
    WHERE RECVD_DATE > getdate() – 10) AS IV
    WHERE IV.Old_Qty < 100.0 AND New_Qty >= 100.0
    GO
    

    One can also join that data set with another table and do processing. The biggest advantage (besides not having another table and another set of steps to achieve the same thing is the fact that we are reducing the number of records that we have to retrieve – previously, if the data set returned would have been large, we would have first stored it in a temp table and then applied a filter on it. If that data set was huge, it would have had performance implications. With this new feature, we can easily reduce that overhead and get only the records that we want/need.

    Use Ouput clause for auditing perpose, One of the best use of output.

    --we will keep "Audit Trail" for below given table
    create table ClientOrder
    (
          OrderID varchar(5)
          ,ClientID varchar(5)
          ,Test varchar(20)
          ,OrderDate datetime default getdate()
    )
    GO
    
    
    --following is the table, we will keep "Audit Trail" in.
    --this will keep track of all data changed
    CREATE TABLE AuditOfOrder
    (
          Id INT Identity(1,1)
          ,OrderID varchar(50)
          ,OldTest varchar(20)
          ,NewTest varchar(20)
          ,DMLPerformed varchar(15)
          ,ChangeDate datetime default getdate()
    )
    GO
    
    --inserting data in "ClientOrder" table
    --all insert will be stored in audit trail table too via "OUTPUT" clause
    INSERT INTO ClientOrder(OrderID,ClientID,Test)
    Output Inserted.OrderID,Inserted.Test,Inserted.Test,'Insert' into AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
    VALUES('A1001','CHEM1','VOCMS Group1')
    GO
    
    
    --inserting data in "ClientOrder" table
    --all insert will be stored in audit trail table too via "OUTPUT" clause
    INSERT INTO ClientOrder(OrderID,ClientID,Test)
    Output Inserted.OrderID,Inserted.Test,Inserted.Test,'Insert' into AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
    VALUES('A1001','CHEM1','Pesticide Group1')
    GO
    
    
    --let us see what we have in both tables now.
    select * from ClientOrder
    select * from AuditOfOrder
    go
    
    
    --we will perform UPDATE on "ClientOrder" table
    --which will be recorded in "AuditOfOrder" table too
    INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
    SELECT
    t.OrderID,
    t.OldTest,
    t.NewTest,
    t.DMLPerformed
    FROM
    (
    UPDATE ClientOrder
          SET Test ='SVOC Stars'
    OUTPUT
          inserted.OrderID AS OrderID,
          deleted.Test AS OldTest,
          inserted.Test AS NewTest,
          'UPDATE' as DMLPerformed
    WHERE ClientOrder.Test='VOCMS Group1'
    ) t
    GO
    
    
    --let us see what we have in both tables now.
    select * from ClientOrder
    select * from AuditOfOrder
    go
    
    
    --Finally the log of Delete will be stored in the same way.
    INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
    SELECT
    t.OrderID,
    t.OldTest,
    t.NewTest,
    t.DMLPerformed
    FROM
    (
    DELETE FROM ClientOrder
    OUTPUT
          deleted.OrderID AS OrderID,
          deleted.Test AS OldTest,
          deleted.Test AS NewTest,
          'DELETE' as DMLPerformed
    WHERE ClientOrder.Test='SVOC Stars'
    ) t
    GO
    
    
    --let us see what we have in both tables now.
    select * from ClientOrder
    select * from AuditOfOrder
    Go
    

    For example You can read Click1

    For example You can read Click2

    Thanks and Good day

    Yogesh

    Replied on Jan 10 2012 12:50AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    7

    In SQL Server 2005 and 2008, you can add an OUTPUT clause to your data manipulation language (DML) statements. The clause returns a copy of the data that you’ve inserted into or deleted from your tables. You can return that data to a table variable, a temporary or permanent table, or to the processing application that’s calls the DML statement. You can then use that data for such purposes as archiving, confirmation messaging, or other application requirements.

    OUTPUT clause has accesses to inserted and deleted tables (virtual tables,magic table) just like triggers. Both SQL Server 2005 and 2008 let you add an OUTPUT clause to INSERT, UPDATE, or DELETE statements. SQL Server 2008 also lets you add the clause to MERGE statements.

    Syntaxt

    <OUTPUT_CLAUSE> ::=
    {
        [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
        [ OUTPUT <dml_select_list> ]
    }
    <dml_select_list> ::=
    { <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
        [ ,...n ]
    
    <column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
        | $action
    

    The OUTPUT clause and the OUTPUT INTO { @tablevariable | outputtable } clause can be defined in a single INSERT, UPDATE, DELETE, or MERGE statement.

    The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.

    When a computed column is included in the , the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.

    There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

    If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

    You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

    Example

     CREATE TABLE t (id INT)
       GO
    
       INSERT INTO t VALUES(1)
       INSERT INTO t VALUES(2)
       INSERT INTO t VALUES(3)
       INSERT INTO t VALUES(4)
       GO
    
       -- this displays what was deleted
       DELETE t
              OUTPUT deleted.id AS 'deleted';
    
       -- this displays what is inserted
       INSERT INTO t
           OUTPUT inserted.id AS 'inserted'
       VALUES(1)
    

    The OUTPUT clause is not supported in the following statements:

    1. DML statements that reference local partitioned views, distributed partitioned views, or remote tables.

    2. INSERT statements that contain an EXECUTE statement.

    3. Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

    4. The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.

    5. A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

    To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

    1. Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.

    2. A column from a view or inline table-valued function when that column is defined by one of the following methods:
      . A subquery.
      . A user-defined function that performs user or system data access, or is assumed to perform such access.
      . A computed column that contains a user-defined function that performs user or system data access in its definition.

    3. When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised. For more information, see MSSQLSERVER_4186.

    Inserting Data Returned From an OUTPUT Clause Into a Table

    The whole operation is atomic. Either both the INSERT statement and the nested DML statement that contains the OUTPUT clause execute, or the whole statement fails.

    1. The following restrictions apply to the target of the outer INSERT statement:

      . The target cannot be a remote table, view, or common table expression.

      . The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.

      . Triggers cannot be defined on the target.

      . The target cannot participate in merge replication or updatable subscriptions for transactional replication.

    2. The following restrictions apply to the nested DML statement:

      . The target cannot be a remote table or partitioned view.

      . The source itself cannot contain a clause.

    3. The OUTPUT INTO clause is not supported in INSERT statements that contain a clause.

    4. @@ROWCOUNT returns the rows inserted only by the outer INSERT statement.

    5. @@IDENTITY, SCOPEIDENTITY, and IDENTCURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.

    6. Query notifications treat the statement as a single entity, and the type of any message that is created will be the type of the nested DML, even if the significant change is from the outer INSERT statement itself.

    7. In the clause, the SELECT and WHERE clauses cannot include subqueries, aggregate functions, ranking functions, full-text predicates, user-defined functions that perform data access, or the TEXTPTR function.

    http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008

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

    Replied on Jan 10 2012 4:29AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    5

    It's funny, but i didn't know about OUTPUT clause before this quiz and was using insert, selcet, but now i know :)

    OUTPUT is usefull when you want to know what changes you just made to a table. For example if you DELETE something from a table you can use OUTPUT to see the rows that were deleted from the table.

    You can use OUTPUT with following statements: INSERT, UPDATE, DELETE, and MERGE

    Bacuase the question was about INSERT and SELECT, I'm going to an INSERT and SELECT example

    --creating our main table
    CREATE TABLE dbo.TEST-Table1
    (
        ID INT,
        FName VARCHAR(32),
        LName VARCHAR(32)
    )
    Go
    
    --insering values to our main table
    INSERT INTO dbo.TEST-Table1 VALUES 
         (1, 'Fred', 'Alexander')
        ,(2, 'Tom', 'Mann')
        ,(3, 'Cindy', 'Peer')
        ,(4, 'Mike', 'Anderson');
    GO
    
    --creating our audit table
    CREATE TABLE dbo.TEST-InsertedItems
    (
        ID INT,
        FName VARCHAR(32),
        LName VARCHAR(32)
    )
    GO
    
    --inseting new values to our main table
    INSERT INTO TEST-Table1(ID, FName ,LName)
    --using OUTPUT clause to insert new values to audit table
    OUTPUT Inserted.* 
    INTO TEST-InsertedItems
    VALUES (5, 'Jeff', 'Marr') 
    
    
    select * from dbo.TEST-Table1
    
    ID  FName	LNam
    1   Fred	Alexander
    2   Tom	Mann
    3   Cindy	Peer
    4   Mike	Anderson
    5   Jeff	Marr
    
    select * from dbo.TEST-InsertedItems
    
    ID  FName	LName
    5   Jeff	Marr
    

    As you can see dbo.TEST-InsertedItems has the insered row.

    OUTPUT clause is useful for audit-trail, so you can keep track of changes.

    Visit http://msdn.microsoft.com/en-us/library/ms177564.aspx for more information.

    Replied on Jan 10 2012 10:36AM  . 
    faran
    2372 · 0% · 5
  • Score
    2

    OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back.

    Normally used in INSERT,DELETE,UPDATE and MERGE.

    Replied on Jan 10 2012 8:03PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    7

    OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

    OUTPUT clause can generate table variable, a permanent table, or temporary table. Even though, @@Identity will still work in SQL Server 2005, however I find OUTPUT clause very easy and powerful to use. Let us understand OUTPUT clause using example.
    ————————————————————————————————————————
    —-Example 1 : OUTPUT clause into Table with INSERT statement
    
    USE AdventureWorks;
    GO
    --------Creating the table which will store permanent table
    CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
    ----Creating temp table to store ovalues of OUTPUT clause
    DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
    ----Insert values in real table as well use OUTPUT clause to insert
    ----values in the temp table.
    INSERT TestTable (ID, TEXTVal)
    OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
    VALUES (1,'FirstVal')
    INSERT TestTable (ID, TEXTVal)
    OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
    VALUES (2,'SecondVal')
    ----Check the values in the temp table and real table
    ----The values in both the tables will be same
    SELECT * FROM @TmpTable
    SELECT * FROM TestTable
    ----Clean up time
    DROP TABLE TestTable
    GO
    ResultSet 1:
    ID TextVal
    ——————— ————————
    1 FirstVal
    2 SecondVal
    
    ID TextVal
    ——————— ———————
    1 FirstVal
    2 SecondVal
    ————————————————————————————————————————
    —-Example 2 : OUTPUT clause with INSERT statement
    ————————————————————————————————————————
    USE AdventureWorks;
    GO
    ----Creating the table which will store permanent table
    CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
    ----Insert values in real table as well use OUTPUT clause to insert
    ----values in the temp table.
    INSERT TestTable (ID, TEXTVal)
    OUTPUT Inserted.ID, Inserted.TEXTVal
    VALUES (1,'FirstVal')
    INSERT TestTable (ID, TEXTVal)
    OUTPUT Inserted.ID, Inserted.TEXTVal
    VALUES (2,'SecondVal')
    ----Clean up time
    DROP TABLE TestTable
    GO
    ResultSet 2:
    ID TextVal
    ——————— ———————
    1 FirstVal
    
    (1 row(s) affected)
    
    ID TextVal
    ——————— ———————
    2 SecondVal
    ————————————————————————————————————————
    —-Example 3 : OUTPUT clause into Table with UPDATE statement
    ————————————————————————————————————————
    USE AdventureWorks;
    GO
    ----Creating the table which will store permanent table
    CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
    ----Creating temp table to store ovalues of OUTPUT clause
    DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))
    ----Insert values in real table
    INSERT TestTable (ID, TEXTVal)
    VALUES (1,'FirstVal')
    INSERT TestTable (ID, TEXTVal)
    VALUES (2,'SecondVal')
    ----Update the table and insert values in temp table using Output clause
    UPDATE TestTable
    SET TEXTVal = 'NewValue'
    OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable
    WHERE ID IN (1,2)
    ----Check the values in the temp table and real table
    ----The values in both the tables will be same
    SELECT * FROM @TmpTable
    SELECT * FROM TestTable
    ----Clean up time
    DROP TABLE TestTable
    GO
    ResultSet 3:
    ID_New TextVal_New ID_Old TextVal_Old
    ——————— ——————— ——————— ———————
    1 NewValue 1 FirstVal
    2 NewValue 2 SecondVal
    
    ID TextVal
    ——————— ———————
    1 NewValue
    2 NewValue
    ————————————————————————————————————————
    —-Example 4 : OUTPUT clause into Table with DELETE statement
    ————————————————————————————————————————
    USE AdventureWorks;
    GO
    
    --Creating the table which will store permanent table
    CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
    ----Creating temp table to store ovalues of OUTPUT clause
    DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
    ----Insert values in real table
    INSERT TestTable (ID, TEXTVal)
    VALUES (1,'FirstVal')
    INSERT TestTable (ID, TEXTVal)
    VALUES (2,'SecondVal')
    ----Update the table and insert values in temp table using Output clause
    DELETE
    FROM TestTable
    OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable
    WHERE ID IN (1,2)
    ----Check the values in the temp table and real table
    ----The values in both the tables will be same
    SELECT * FROM @TmpTable
    SELECT * FROM TestTable
    ----Clean up time
    DROP TABLE TestTable
    GO
    ResultSet 4:
    ID TextVal
    ——————— ———————
    1 FirstVal
    2 SecondVal
    
    ID TextVal
    ——————— ———————
    If we run all the above four example, you will find that OUTPUT clause is very useful. 
    
    The OUTPUT clause may also be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.
    
    When a computed column is included in the <dml_select_list>, the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.
    
    There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.
    
    If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.
    
    You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.
    
    The OUTPUT clause is not supported in the following statements:
    
        DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
    
        INSERT statements that contain an EXECUTE statement.
    
        Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
    
        The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
    
        A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
    
    To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:
    
        Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.
    
        A column from a view or inline table-valued function when that column is defined by one of the following methods:
    
            A subquery.
    
            A user-defined function that performs user or system data access, or is assumed to perform such access.
    
            A computed column that contains a user-defined function that performs user or system data access in its definition.
    
    Replied on Jan 11 2012 12:11AM  . 
    dips
    928 · 0% · 30
  • Score
    4

    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.

    OUTPUT clause can be used in

    1. DELETE
    2. INSERT
    3. UPDATE
    4. MERGE

    We can use OUTPUT clause as follows:

    DECLARE @Records TABLE
    (  Column1 UNIQUEIDENTIFIER )
    
    DELETE  FROM Table1
    OUTPUT  deleted.Column1
      INTO @Records ( Column1 )
    WHERE Col2 = 11
    
    SELECT COUNT(*) AS AffectedRecordCount FROM    @Records
    

    The OUTPUT clause is not supported in the following statements:

    • DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
    • INSERT statements that contain an EXECUTE statement.
    • Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
    • The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
    • A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
    Replied on Jan 11 2012 4:08AM  . 
    Hardik Doshi
    20 · 9% · 2853
  • Score
    6

    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.

    OUTPUT clause can generate table variable, a permanent table, or temporary table.

    Using an OUTPUT Clause in an INSERT Statement

    When you insert data into a table, you can use the OUTPUT clause to return a copy of the data that’s been inserted into the table. The OUTPUT clause takes two basic forms: OUTPUT and OUTPUT INTO. Use the OUTPUT form if you want to return the data to the calling application. Use the OUTPUT INTO form if you want to return the data to a table or a table variable.

    Create Table MyTable (ID int, name varchar(100),createdAt datetime)
    
    -- declare @InsertOutput2 table variable 
    DECLARE @InsertOutput2 table
    (
      ID int,
      name nvarchar(50),
      CreatedAt datetime
    );
    
     -- insert new row into table in your database
    INSERT INTO MyTable
    OUTPUT 
        INSERTED.ID, 
        INSERTED.Name, 
        INSERTED.CreatedAt
      INTO @InsertOutput2
    VALUES(111, 'Some text', GETDATE());
    
    
    -- view inserted row in table
    SELECT * FROM MyTable;
    -- view output row in @InsertOutput2 variable
    SELECT * FROM @InsertOutput2;
    
    drop table MyTable
    

    Using an OUTPUT Clause in an UPDATE Statement

    Create Table MyTable (ID int, name varchar(100),createdAt datetime)
    
    -- declare @InsertOutput2 table variable 
    DECLARE @UpdateOutput1 table
    (
      ID int,  
      name nvarchar(50),
      Oldname nvarchar(50),
      CreatedAt datetime,
      UpdatedAt datetime
    );
    
     -- insert new row into table in your database
    INSERT INTO MyTable
    VALUES(111, 'Some text', GETDATE());
    
    
    UPDATE MyTable
    SET   
      name = 'Changed...'
    OUTPUT    
        INSERTED.ID,
        INSERTED.Name,
        DELETED.Name,
        DELETED.CreatedAt,
        getdate()
      INTO @UpdateOutput1
    WHERE Name = 'Some text';
    
    
    
    -- view inserted row in table
    SELECT * FROM MyTable;
    -- view output row in @InsertOutput2 variable
    SELECT * FROM @UpdateOutput1;
    
    drop table MyTable
    

    In the same manner you can use it with DELETE statement. It uises the same INSERTED and DELETED memory tables.

    Using an OUTPUT Clause in an MERGE Statement

    When working in SQL Server 2008, you can add an OUTPUT clause to a MERGE statement. The process is similar to adding the clause to an UPDATE statement; you use both the INSERTED and DELETED column prefixes.

    Let’s look at an example that demonstrates how this work. First, however, we must create a second table to support the MERGE statement. The following script creates the Book2 table and populates it with two rows:

    CREATE TABLE dbo.Books2
    (
      BookID int NOT NULL PRIMARY KEY,
      BookTitle nvarchar(50) NOT NULL,
      ModifiedDate datetime NOT NULL
    );
    
    
    CREATE TABLE dbo.Books
    (
      BookID int NOT NULL PRIMARY KEY,
      BookTitle nvarchar(50) NOT NULL,
      ModifiedDate datetime NOT NULL
    );
    
    
    
    INSERT INTO Books2
         VALUES(101, '100 Years of Solitude', GETDATE());
    
    
    
    INSERT INTO Books2
         VALUES(102, 'Pride & Prejudice', GETDATE());
    
    --Once we’ve created the Books2 table, we can try a MERGE statement. In the following example, I declare the @MergeOutput1 variable, merge data from the Books table into the Books2 table, and view the results:
    -- declare @MergeOutput1 table variable
    
    DECLARE @MergeOutput1 table
    (
      ActionType nvarchar(10),
      BookID int,
      OldBookTitle nvarchar(50),
      NewBookTitle nvarchar(50),
      ModifiedDate datetime
    );
    
    
    INSERT INTO Books
         VALUES(101, 'The Great Gatsby', GETDATE());
    
    
    -- use MERGE statement to perform update on Book2
    MERGE Books2 AS b2 
    USING Books AS b1
    ON (b2.BookID = b1.BookID)
    WHEN MATCHED
    THEN UPDATE 
    SET b2.BookTitle = b1.BookTitle
    OUTPUT
        $action,
        INSERTED.BookID,
        DELETED.BookTitle,
        INSERTED.BookTitle,
        INSERTED.ModifiedDate
      INTO @MergeOutput1;
    
    
    -- view Books table
    SELECT * FROM Books; 
    
    -- view updated rows in Books2 table
    SELECT * FROM Books2; 
    
    -- view output rows in @MergeOutput1 variable
    SELECT * FROM @MergeOutput1;
    
    
    
    drop table Books
    drop table Books2
    

    $action Gives us the type of action that is performed by Merge statement per record.

    Replied on Jan 11 2012 11:50PM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    8

    OUTPUT Cluase

    When we try to execute any DML statements, such as inserting a row, DML statements do not produce any results (apart from showing messages). OUTPUT clause helps to get it. The clause returns a copy of the data that you’ve inserted into or deleted from your tables. You can return that data to a table variable, a temporary or permanent table, or to the processing application that’s calls the DML statement.

    In fact, all deleted or inserted information gets stored in a virtual table. By using a simple SELECT statement, we can retrieve all of the information available in the virtual table and display it to the screen. You can use the OUTPUT clause together with the inserted and deleted virtual tables, as you might use a trigger. But please be aware that the OUTPUT clause must be used with an INTO expression to fill a table.

    OUTPUT clause with INSERT statement

    single INSERT statement Following example, insert a single row into the table and display the result on the screen.

    Use Northwind
    
    Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))
    
    Insert Region 
    Output inserted.regionid,inserted.regiondescription into @ins
    values(5,'NorthEast')
    
    --Display Newly Inserted Value will be displayed
    Select * from @ins
    
    --Displays all values along with newly Inserted value will be displayed
    
    Select * from Region
    

    multiple INSERT statements

    Use Northwind
    
    Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))
    
    Insert Region(RegionID,RegionDescription) 
    Output inserted.Regionid,inserted.RegionDescription into @ins
    values(7,'NorthWest')
    
    Insert Region(RegionID,RegionDescription) 
    Output inserted.Regionid,inserted.RegionDescription into @ins
    values(8,'SouthEast')
    
    Insert Region(RegionID,RegionDescription) 
    Output inserted.Regionid,inserted.RegionDescription into @ins
    values(9,'NorthEast')
    
    
    Insert Region 
    Output inserted.Regionid,inserted.RegionDescription into @ins
    values(10,'SouthWest')
    
    --Display Newly Inserted Value will be displayed
    Select * from @ins
    
    --Displays all values along with newly Inserted values will be displayed
    
    Select * from Region
    

    OUTPUT clause with single DELETE statement

    Use Northwind
    
    Declare @del as table(delRegionID int,delRegionDescription nchar(50))
    
    Delete  region
    Output deleted.regionid,deleted.regiondescription
    into @del
    Where regionid=7
    
    --Display Deleted Value will be displayed
    
    Select * from @del
    

    OUTPUT clause with single UPDATE statement

    Use Northwind
    
    Declare @upd as table(UpRegionID int,UpRegionDescription nchar(50),UpNewRegionDescription nchar(50))
    
    Update region
    Set RegionDescription='NorthernWest'
    Output inserted.regionid,deleted.regiondescription,inserted.
    regiondescription
    into @upd
    Where regionid=6
    
    --Display Newly Updated Value will be displayed
    Select * from @upd
    
    --Displays all values along with newly Updated value will be displayed
    
    Select * from Region
    

    OUTPUT Clause in a MERGE Statement

    The following script creates the Book2 table and populates it with two rows:

    CREATE TABLE dbo.Books2(
      BookID int NOT NULL PRIMARY KEY,
      BookTitle nvarchar(50) NOT NULL,
      ModifiedDate datetime NOT NULL
    );
    
    INSERT INTO Books2
    VALUES(101, '100 Years of Solitude', GETDATE());
    
    INSERT INTO Books2
    VALUES(102, 'Pride & Prejudice', GETDATE());
    

    Once we’ve created the Books2 table, we can try a MERGE statement. In the following example, I declare the @MergeOutput1 variable, merge data from the Books table into the Books2 table, and view the results:

    -

    - declare @MergeOutput1 table variable
    DECLARE @MergeOutput1 table
    (
      ActionType nvarchar(10),
      BookID int,
      OldBookTitle nvarchar(50),
      NewBookTitle nvarchar(50),
      ModifiedDate datetime
    );
    
    -- use MERGE statement to perform update on Book2
    MERGE Books2 AS b2 
    USING Books AS b1
    ON (b2.BookID = b1.BookID)
    WHEN MATCHED
    THEN UPDATE 
    SET b2.BookTitle = b1.BookTitle
    OUTPUT
        $action,
        INSERTED.BookID,
        DELETED.BookTitle,
        INSERTED.BookTitle,
        INSERTED.ModifiedDate
      INTO @MergeOutput1;
    
    -- view Books table
    SELECT * FROM Books;
    
    -- view updated rows in Books2 table
    SELECT * FROM Books2;
    
    -- view output rows in @MergeOutput1 variable
    SELECT * FROM @MergeOutput1;
    

    Here we used $action variable. $action is a built-in parameter returns one of three nvarchar(10) values—INSERT, UPDATE, or DELETE—and is available only to the MERGE statement. The value returned depends on the action performed on the row.

    Points To Remember

    When a computed column is included in the dmlselectlist, the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed. There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond. If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

    Limitations

    I if the DML statement fails, for example, due to a constraint violation, the OUTPUT clause would still return the rows that would have been inserted or deleted. Therefore, we should never rely on the OUTPUT statement to indicate that the statement was successful. Your database application should check for errors from DML statements. If the application encounters an error, the OUTPUT results should not be used.

    The OUTPUT clause is not supported in the following statements:

    • DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
    • INSERT statements that contain an EXECUTE statement.
    • Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
    • The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
    • A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

    To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

    • Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.
    • A column from a view or inline table-valued function when that column is defined by one of the following methods:

      1.A subquery.

      2.A user-defined function that performs user or system data access, or is assumed to perform such access.

      3.A computed column that contains a user-defined function that performs user or system data access in its definition. When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised.

    References

    http://www.aspfree.com/c/a/MS-SQL-Server/Wonders-of-the-OUTPUT-Clause-in-SQL-Server-2005/

    http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/

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

    Replied on Jan 18 2012 3:36AM  . 
    Sineetha
    106 · 2% · 492
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.