UPDATE with OUTPUT CLAUSE

  • 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

  • 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

  • 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

  • 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]

  • 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

  • Thanks for the work around.

  • 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

  • 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. 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.

  • 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

  • :blush:

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

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