SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The OUTPUT Clause for INSERT & DELETE Statements


The OUTPUT Clause for INSERT & DELETE Statements

Author
Message
Amarendra Reddy Thummeti
Amarendra Reddy Thummeti
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1270 Visits: 132
Comments posted to this topic are about the item The OUTPUT Clause for INSERT & DELETE Statements

Thanks,
AMAR
amarreddy23@gmail.com
Steve Hall
Steve Hall
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18726 Visits: 13516
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
psmith-502573
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 93
I see UPDATE mentioned in the article but no references as to how to use it with OUTPUT
gregg_dn
gregg_dn
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1120 Visits: 1554
Thank you for a very informative article.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)

Group: General Forum Members
Points: 975172 Visits: 49307
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

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqlfriends
sqlfriends
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51024 Visits: 4834
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
MadMyche
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 84
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
billo_intersoft
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 20
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
MadMyche
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 84
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

Amarendra Reddy Thummeti
Amarendra Reddy Thummeti
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1270 Visits: 132
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
Amarendra Reddy Thummeti
Amarendra Reddy Thummeti
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1270 Visits: 132
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
Amarendra Reddy Thummeti
Amarendra Reddy Thummeti
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1270 Visits: 132
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
Toby Harman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 758
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
liebesiech
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2399 Visits: 930
Thank you for this informative post! It comes in very handy when documenting data manipulations with queries!
Scott Coleman
Scott Coleman
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26479 Visits: 2391
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.



ingenious
ingenious
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 76
excellent article.!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search