The OUTPUT Clause for INSERT & DELETE Statements

  • Comments posted to this topic are about the item The OUTPUT Clause for INSERT & DELETE Statements

    Thanks,
    AMAR
    amarreddy23@gmail.com

  • There is also the slightly different format of the OUTPUT clause within the MERGE statement - Making use of the $Action variable

  • I see UPDATE mentioned in the article but no references as to how to use it with OUTPUT

  • Thank you for a very informative article.

  • Gosh... this article started out good with code that you could copy'n'paste to setup the test table.  Why didn't you continue that with the remaining code examples instead of using graphics that people have to type from?  My recommendation would be to work with the folks at SQLServerCentral.com with either a resubmittal with code windows instead of graphics or attaching the code to the article in the "Resources" section.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I also found this restriction is a little hard to deal with:
    The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.

  • psmith-502573 - Thursday, May 25, 2017 6:04 AM

    I see UPDATE mentioned in the article but no references as to how to use it with OUTPUT

    MIcrosoft Documentation: UPDATE (Transact-SQL)

    Useful for historical logging, the values present have the before and after values for the columns that are changed using the monikers of deleted and inserted


    DECLARE  @Output TABLE ( ValueOld VARCHAR(100), ValueNew VARCHAR(100) )

    UPDATE   TableName
    SET      ColumnName = @NewValue
    OUTPUT   deleted.ColumnName, inserted.ColumnName INTO @Output
    WHERE    TableIndex = @TableIndex

  • FWIW I did some playing...  this is the script I used (pardon the JUNK prefix, it simply reminds me to clean this up when I am done)

    IF OBJECT_ID ('JUNKDepartment_SRC', 'U') IS NOT NULL
    DROP TABLE dbo.JUNKDepartment_SRC;

    CREATE TABLE [dbo].[JUNKDepartment_SRC](
       [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
       [Name] varchar(50) NOT NULL,
       [GroupName] varchar(50) NOT NULL,
       [ModifiedDate] [datetime] NOT NULL
    ) ON [PRIMARY]

    declare @chgs table(
       [DepartmentID] [smallint],
       [Name] varchar(50) ,
       [GroupName] varchar(50) ,
       [ModifiedDate] [datetime]
    )

    Insert into [dbo].[JUNKDepartment_SRC]([Name],[GroupName],[ModifiedDate])
    Values('Engineering','Research and Development','5/24/2017' );

    insert into [dbo].[JUNKDepartment_SRC]([Name],[GroupName],[ModifiedDate])
    output inserted.DepartmentID, inserted.name, inserted.GroupName, inserted.ModifiedDate
    Values('Sales','Sales & Marketing',getdate());

    -- this is something different I tried 
    update [dbo].[JUNKDepartment_SRC]
    set [GroupName] = 'Sales, Marketing & Promos'
    output inserted.* into @chgs
    where [DepartmentID] = 2

    delete [dbo].[JUNKDepartment_SRC]
    output deleted.* into @chgs
    where [DepartmentID] = 1

    select * from @chgs
    -- the last query returns this
    DepartmentID    Name    GroupName                          ModifiedDate
    2                      Sales    Sales, Marketing & Promos    2017-05-25 11:36:18.830
    1                   Engineering    Research and Development    2017-05-24 00:00:00.000

    It works on UPDATE too. Enjoy

  • sqlfriends - Thursday, May 25, 2017 10:24 AM

    I also found this restriction is a little hard to deal with:
    The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.

    Adversity is the key to innovation and broadening your world...

    While you may not directly insert  to targets with Foreign Keys, you can use a temp/variable table as an intermediary and then migrate to the FK inflected target

  • BrainDonor - Thursday, May 25, 2017 2:01 AM

    There is also the slightly different format of the OUTPUT clause within the MERGE statement - Making use of the $Action variable

    There are couple more articles which will be publishing soon, you will see OUTPUT for MERGE on one of it.

    Thanks,
    AMAR
    amarreddy23@gmail.com

  • Jeff Moden - Thursday, May 25, 2017 8:55 AM

    Gosh... this article started out good with code that you could copy'n'paste to setup the test table.  Why didn't you continue that with the remaining code examples instead of using graphics that people have to type from?  My recommendation would be to work with the folks at SQLServerCentral.com with either a resubmittal with code windows instead of graphics or attaching the code to the article in the "Resources" section.

    Sure will do that. Thanks for your feedback.

    Thanks,
    AMAR
    amarreddy23@gmail.com

  • psmith-502573 - Thursday, May 25, 2017 6:04 AM

    I see UPDATE mentioned in the article but no references as to how to use it with OUTPUT

    There are couple more articles which will be publishing soon, you will see OUTPUT for Update on one of it.

    Thanks,
    AMAR
    amarreddy23@gmail.com

  • Thanks for reminding me of this!

    I built a fairly extensive custom ETL system a few years ago which used MERGE and OUTPUT clause to audit the actions and calculate rowcounts. A little custom error handling and monitoring and the whole system was basically self monitoring and  could raise alerts when data failed to flow.

  • Thank you for this informative post! It comes in very handy when documenting data manipulations with queries!

  • I have used DELETE with OUTPUT to implement FIFO queue behavior.  In one atomic operation it can delete the first row from the queue table and return the values from that row to a table variable.  Very handy when multiple parallel processes are consuming queue entries.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply