UPDATE with OUTPUT CLAUSE

Question

Post reply

UPDATE with OUTPUT CLAUSE

Simon Hundleby

SSCarpal Tunnel

Points: 4553
More actions
November 4, 2015 at 6:58 am

#399273

Is it possible to pivot the output data into multiple rows?
I wanted one row for deleted data and another row for Inserted data, I was looking
at UNION ALL and CROSS APPLY but to no avail.
I just wonder if anyone knew away around this.
Thanks
Simon

Viewing 11 posts - 1 through 10 (of 10 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I'm sorry, there is not enough information to answer your question.

If you want sensible answers and fast turnaround on your questions, see the instructions here[/url].

-- Gianluca Sartori

Simon Hundleby SSCarpal Tunnel Points: 4553 More actions · Answer 2

Sorry I don't have any data/schema as it was just a general question

regarding an UPDATE statement with an OUTPUT Clause.

I wanted to do something like this

use tempdb

GO

CREATE TABLE #Temp1 (ID INT ,location VARCHAR(100))

INSERT INTO #Temp1(ID, Location)

VALUES

(1,'Manchester'),

(2,'London'),

(3,'New york'),

(4,'Dallas')

DECLARE @OUTPUT TABLE ([Action] VARCHAR(10), ChangedValue VARCHAR(100))

UPDATE t1

SET t1.Location ='Pheonix'

OUTPUT

'NewValue', INSERTED.*

UNION ALL-------------Obviously I can't use UNION ALL

'oldValue', DELETED.* INTO @Output

FROM #Temp1 t1

WHERE t1.ID =4

--Result to look like this

--Action Changedvalue

-------------------------

--Newvalue,pheonix

--oldvalue,Dallas

DROP TABLE #Temp1

However I can't use a UNION ALL or CROSS APPLY within an OUTPUT Clause,

so I was wondering if anyone had a way of Pivoting the output data.

Thanks

Simon

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

You cannot use sub-query in OUTPUT, but you can do it with intermediate "always empty" table.

Here is self-explanatory example:

create table src (col int)

go

insert src select 1

insert src select 2

go

create table updhistory(act char(1), col int)

go

create table updhistoryIntermidiate (colD int, colI int)

go

create trigger tiupdhistory on updhistoryIntermidiate instead of insert

as

begin

insert updhistory

select 'D', colD

from inserted

union all

select 'I', colI

from inserted

end

go

if object_id('tempdb..#t') is not null drop table #t

select * into #t from updhistoryIntermidiate

update src set col = col + 10

output deleted.col, inserted.col INTO #t

insert updhistoryIntermidiate select * from #t

go

select * from src

select * from updhistoryIntermidiate

select * from updhistory

Also note, you cannot use updhistoryIntermidiate in OUTPUT directly as it has a trigger on it.

So, you will need to output to temp copy of it then copy output updhistoryIntermidiate which will redirect to your "pivoted" updhistory table

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

drew.allen SSC Guru Points: 76944 More actions · Answer 4

The OUTPUT clause is very limited. You can either output the data directly to the client application or you can output your data to a table/table variable. Period. If you want to manipulate the output, you'll need to output your data to a table/table variable and then query that table/table variable.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Simon Hundleby SSCarpal Tunnel Points: 4553 More actions · Answer 5

Simon Hundleby

SSCarpal Tunnel

Points: 4553

November 4, 2015 at 8:30 am

#1837687

Thanks for the work around.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

drew.allen (11/4/2015)
The OUTPUT clause is very limited. You can either output the data directly to the client application or you can output your data to a table/table variable. Period. If you want to manipulate the output, you'll need to output your data to a table/table variable and then query that table/table variable.
Drew

Composable DML lifts some of those restrictions. For our team, being able to augment the OUTPUT columns with other data and then filter before inserting into another table saves a very useful amount of time and complexity.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

ChrisM@Work (11/4/2015)
drew.allen (11/4/2015)
The OUTPUT clause is very limited. You can either output the data directly to the client application or you can output your data to a table/table variable. Period. If you want to manipulate the output, you'll need to output your data to a table/table variable and then query that table/table variable.
Drew
Composable DML lifts some of those restrictions. For our team, being able to augment the OUTPUT columns with other data and then filter before inserting into another table saves a very useful amount of time and complexity.

Would be great if you could join or cross apply to "update" sub-query, but it is not allowed 🙁

So, it would not work as:

insert into updhistory

select ca.act,ca.val

from ( update src set col = col + 10

output deleted.col colD, inserted.col colI ) q

cross apply (select 'D', colD from q union all select 'I', colI from q) ca(act, val)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

j-1064772 SSCertifiable Points: 5316 More actions · Answer 8

-> J. Drew Allen

Composable DML [/url]lifts some of those restrictions. For our team, being able to augment the OUTPUT columns with other data and then filter before inserting into another table saves a very useful amount of time and complexity.

Thanks, I had never heard of this capability. Learn something new every day on this forum.

drew.allen SSC Guru Points: 76944 More actions · Answer 9

j-1064772 (11/6/2015)
-> J. Drew Allen
Composable DML [/url]lifts some of those restrictions. For our team, being able to augment the OUTPUT columns with other data and then filter before inserting into another table saves a very useful amount of time and complexity.
Thanks, I had never heard of this capability. Learn something new every day on this forum.

Thanks, but that was ChrisM.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

j-1064772 SSCertifiable Points: 5316 More actions · Answer 10

j-1064772

SSCertifiable

Points: 5316

November 6, 2015 at 2:38 pm

#1838354

:blush: