The OUTPUT Clause for INSERT & DELETE Statements

  • amarreddy23

    Ten Centuries

    Points: 1311

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

    Thanks,
    AMAR
    amarreddy23@gmail.com

  • BrainDonor

    SSCoach

    Points: 19202

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

    Steve Hall
    Linkedin
    Blog Site

  • psmith-502573

    SSC Enthusiast

    Points: 178

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

  • gregg_dn

    Ten Centuries

    Points: 1193

    Thank you for a very informative article.

  • Jeff Moden

    SSC Guru

    Points: 994667

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • sqlfriend

    SSC Guru

    Points: 52335

    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.

  • MadMyche

    SSC Enthusiast

    Points: 102

    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

  • billo_intersoft

    SSC Journeyman

    Points: 79

    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

  • MadMyche

    SSC Enthusiast

    Points: 102

    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

  • amarreddy23

    Ten Centuries

    Points: 1311

    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

  • amarreddy23

    Ten Centuries

    Points: 1311

    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

  • amarreddy23

    Ten Centuries

    Points: 1311

    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

  • Toby Harman

    SSCarpal Tunnel

    Points: 4128

    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.

  • liebesiech

    SSCrazy

    Points: 2448

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

  • Scott Coleman

    One Orange Chip

    Points: 27393

    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 16 total)

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