Getting more done with OUTPUT

Question

Post reply

Getting more done with OUTPUT

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
November 16, 2016 at 10:14 pm

#323008

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]

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

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

Michael Lysons SSCertifiable Points: 6490 More actions · Answer 1

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: 198 More actions · Answer 2

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 SSC Eights! Points: 903 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

November 17, 2016 at 7:38 am

#1913068

Good article. We use this quite often.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 6

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: 198 More actions · Answer 7

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

Thanks for the expedited reply

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 8

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 More actions · Answer 9

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 distinctCustomers.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 More actions · Answer 10

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 SSC-Insane Points: 20396 More actions · Answer 11

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: 2823 More actions · Answer 12

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 SSC Eights! Points: 989 More actions · Answer 13

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: 61136 More actions · Answer 14

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