Getting more done with OUTPUT

  • Kenneth.Fisher

    SSCoach

    Points: 19597

    Comments posted to this topic are about the item Getting more done with OUTPUT

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Michael Lysons

    SSCertifiable

    Points: 6472

    It's great little articles like this that make me realise how little I know.

    So, thanks for the education 🙂

  • chris.curtis 19975

    SSC Enthusiast

    Points: 174

    Thanks for this great article, It just so happens i had been training up our Junior developers on using the output clause on a merge statement so this adds extra value.

    One issue that i'm running into is inserting into an archive table with a primary key, even with identity_insert set to on i get the standard error message for explicit value for the identity column can only be specified when column list is used and id_insert is on. Do you know of a way to get this working with identitys?

  • louie1487 78804

    Right there with Babe

    Points: 785

    I use OUPTPUT all the time.

    Why it never occurred to use it when archiving our production tables is beyond me.

    Guess who is able to save himself more code. This guy.

    Thanks for the article. It is knowledge like this that people assume everyone knows.

  • Alex Grinberg-230981

    SSC Eights!

    Points: 897

    Great article. However, it could be even better, if you add OUTPUT in combination with MERGE statement to provide INSERT for third normal form. Do you have such as example? If yes, please share.

    Thank you!

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good article. We use this quite often.

  • Kenneth.Fisher

    SSCoach

    Points: 19597

    Curtis: The INTO part of the OUTPUT clause has a field list. If you include the field list the insert into a table with an identity column will work. Quick demo:

    CREATE TABLE Source (col1 int, col2 int)

    INSERT INTO Source VALUES (1,1),(1,1),(1,1),(1,1)

    CREATE TABLE Archive (Id int NOT NULL identity(1,1), col1 int, col2 int)

    DELETE FROM source

    OUTPUT deleted.col1, deleted.col2

    INTO archive (col1, col2)

    Great idea for a blog post btw so thanks for that! 🙂

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • chris.curtis 19975

    SSC Enthusiast

    Points: 174

    Ken, totally makes sense. I'll pass this info off to our Junior Devs.

    Thanks for the expedited reply

  • Kenneth.Fisher

    SSCoach

    Points: 19597

    Alex: Unfortunately I'm not sure what you are asking for? The only main difference between using OUTPUT with a MERGE is that there is a $action value that you can include in your list of fields to tell you if the row affected came from the insert, update, or delete.

    Can you explain further what you are looking for?

    Thanks

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Alex Grinberg-230981

    SSC Eights!

    Points: 897

    Let's take for example Nothwind database. There are tables Products, [Order Details], and Orders. We need to insert new batch of orders. My sample below doing that in two steps. However, there was an article on SQLServerCnetral, where MERGE reduced one step. I can't find it, and don't remember the author.

    declare @x xml, @start datetime2 = getdate()

    -- get XML

    SELECT @x =

    (

    SELECT Products.ProductName,

    Customers.CompanyName,

    Shippers.CompanyName AS ShippersName,

    convert(varchar(20),GETDATE(),101) as OrderDate,

    [Order Details].UnitPrice,

    [Order Details].Quantity,

    CAST([Order Details].Discount as varchar(9)) as Discount

    FROM Products INNER JOIN

    [Order Details] ON Products.ProductID = [Order Details].ProductID INNER JOIN

    Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN

    Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN

    Shippers ON Orders.ShipVia = Shippers.ShipperID

    WHERE Orders.OrderDate = '1998-05-05'

    FOR XML PATH('Product'), ROOT('Orders')

    )

    -- Created output storage table

    DECLARE @NewOrder TABLE(OrderID int, CustomerID nchar(5))

    -- INSERT into Orders table

    INSERT Orders (CustomerID, OrderDate, ShipVia)

    OUTPUT inserted.OrderID, inserted.CustomerID INTO @NewOrder (OrderID, CustomerID)

    SELECT distinct Customers.CustomerID,

    c.value('OrderDate[1]', 'datetime') as OrderDate,

    Shippers.ShipperID

    FROM @x.nodes('Orders/Product') as T(C)

    JOIN Products ON Products.ProductName = c.value('ProductName[1]', 'nvarchar(100)')

    JOIN Shippers ON Shippers.CompanyName = c.value('ShippersName[1]', 'nvarchar(100)')

    JOIN Customers ON Customers.CompanyName = c.value('CompanyName[1]', 'nvarchar(100)')

    -- INSERT into Order Details table

    INSERT [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)

    SELECT NewOrder.OrderID, Products.ProductID,

    c.value('UnitPrice[1]', 'money') as UnitPrice,

    c.value('Quantity[1]', 'int') as Quantity,

    c.value('Discount[1]', 'real') as Discount

    FROM @x.nodes('Orders/Product') as T(C)

    JOIN Products ON Products.ProductName = c.value('ProductName[1]', 'nvarchar(100)')

    JOIN Shippers ON Shippers.CompanyName = c.value('ShippersName[1]', 'nvarchar(100)')

    JOIN Customers ON Customers.CompanyName = c.value('CompanyName[1]', 'nvarchar(100)')

    JOIN @NewOrder NewOrder ON NewOrder.CustomerID = Customers.CustomerID

  • johnpisg

    SSC Rookie

    Points: 37

    Excellent article! however I have one question: in the example you use OUTPUT with the DELETE command, you're still using the explicit TRANSACTION, why? what happend if you do not wrap this in the transaction?

  • Kenneth.Fisher

    SSCoach

    Points: 19597

    I'll be honest it was just habit/copy paste. It's a single command so it has it's own implicit transaction. Absolutely no effect if you leave the transaction off in this case.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Rick-153145

    SSCrazy

    Points: 2683

    I have a question on the use of output with merge. If you have a stored proc that outputs the data to another table (lets say an audit table), is there a way around the issue of updating a value to NULL?

    So, usually you would use the MERGE like so:

    MERGE <blah> dest

    using <blahblahblah> As source

    when matched then update

    ....

    OUTPUT

    ISNULL( INSERTED.Id, DELETED.Id) AS Id

    , ISNULL( INSERTED.Blah , DELETED.blah) AS blah

    , (CASE

    WHEN DELETED.Id IS NULL AND INSERTED.Id IS NOT NULL THEN 'I'

    WHEN DELETED.Id IS NOT NULL AND INSERTED.Id IS NOT NULL THEN 'U'

    WHEN DELETED.Id IS NOT NULL AND INSERTED.Id IS NULL THEN 'D'

    ELSE ''

    END) AS AuditType

    INTO

    aTable;

    Now, if blah was not null and you are updating it to null, this output wouldn't care, it would simply not set it to NULL. I know I can use a CASE on each side, but this gets very very convoluted very quickly with large tables. Is there a better way to do this?

  • Peter Heller

    Right there with Babe

    Points: 785

    Thanks for a great article. I am looking into using the output statement to determine primary keys after insertion plus the latest row version with concurrency. It works independently of the implementation identity or sequence objects on tables. I am throwing it out there to see if others are thinking about potentially using this as a technique. Greatly appreciate any feedback.

    drop table if exists dbo.MyTest;

    --create TABLE MyTest (myKey [int] identity(1,1) not null primary key, myValue int, RowVersion rowversion);

    create TABLE MyTest (myKey [int] primary key, myValue int, RowVersion rowversion);

    go

    DECLARE @TrackPrimaryKeyAndRowVersion TABLE (SPID smallint, myKey int,VerCol binary(8));

    INSERT INTO MyTest (myKey, myValue)

    output @@Spid,Inserted.myKey,Inserted.RowVersion into @TrackPrimaryKeyAndRowVersion(SPID,myKey,VerCol)

    values (1, 5);

    INSERT INTO MyTest (myKey, myValue)

    output @@Spid,Inserted.myKey,Inserted.RowVersion into @TrackPrimaryKeyAndRowVersion(SPID,myKey,VerCol)

    VALUES (2, 0);

    SELECT * FROM MyTest as mt

    SELECT * FROM @TrackPrimaryKeyAndRowVersion as tpkarv

  • Alan Burstein

    SSC Guru

    Points: 61064

    Excellent article! Well done.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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