The OUTPUT Clause for INSERT & DELETE Statements

Question

The OUTPUT Clause for INSERT & DELETE Statements

amarreddy23

Ten Centuries

Points: 1361
More actions
May 24, 2017 at 10:50 pm

#343016

Comments posted to this topic are about the item The OUTPUT Clause for INSERT & DELETE Statements
Thanks,
AMAR
amarreddy23@gmail.com

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

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

BrainDonor SSCoach Points: 19254 More actions · Answer 1

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

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

gregg_dn SSCommitted Points: 1596 More actions · Answer 3

gregg_dn

SSCommitted

Points: 1596

May 25, 2017 at 6:36 am

#1944148

Thank you for a very informative article.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

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)

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

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

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

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

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

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

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

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

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

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 14

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.