SQL Server : splitting a row into multiple rows from OUTPUT clause

Question

I am trying to get a query that can help me achieve something similar to the image below:

The first table is the result of a SQL OUTPUT clause (both inserted and deleted). I would like to split these results into a new table (like the second one in the above image). So I want to have one row depicting the inserted records and the second row depicting the deleted records.

This is how my sample data was created:

select 
    inserted_ID = 1,
    inserted_name = 'Brian',
    inserted_phone = '123-456-7890',
    operation_type1 = 'inserted',
    deleted_id = 2,
    deleted_name = 'James',
    deleted_phone = '222-222-2222',
    operation_type2 = 'Deleted'
into 
    #tbltest

Please post a Minimal, Complete, and Verifiable example also check How to ask. — Wagner DosAnjos
– Wagner DosAnjos, Commented Jan 16, 2017 at 18:23
Why do you have a table structure like this in the first place? It seems "odd" to have Inserted_ID and Deleted_ID on the same row. Is there any actual / natural relationship between those two? — Solomon Rutzky
– Solomon Rutzky, Commented Jan 16, 2017 at 19:11
Hi @srutzky. This is really just an example. It's not a real table in my database. Also, when using the OUTPUT clause with an UPDATE statement, SQL Server allows us to show both the inserted record and the deleted record. — Abayomi
– Abayomi, Commented Jan 16, 2017 at 19:13
@Solution Yes, the OUTPUT clause allows access to both inserted and deleted records during an UPDATE statement, which is exactly what I stated in my answer. BUT, even if you access both inserted and deleted values, per each row coming out of OUTPUT, inserted and deleted represent the same row: inserted is the "current" value of each column, and `deleted" is the "old" value of each column. But they will never be different rows. Hence this separation that you are asking for only makes sense if wanting "before" and "after" versions of a row to be in diff rows in your table. — Solomon Rutzky
– Solomon Rutzky, Commented Jan 16, 2017 at 19:50
Also, it would help to know what you are actually trying to accomplish with this. It could be that there is a better method that is not being presented because you were too specific in describing the problem with your implementation, rather than the original problem that lead you to your implementation. For example, if you are wanting to audit modifications to a table, a Trigger would be easier and better than all of this extra code that needs to be replicated per each DML statement. — Solomon Rutzky
– Solomon Rutzky, Commented Jan 16, 2017 at 20:29

marc_s · Accepted Answer · 2017-01-16 19:40:17Z

3

Try to organize your table structure something like (keep attention on [uid]):

create table [my_table]
(
     [id]       int
    ,[name]     nvarchar(256)
    ,[phone]    nvarchar(256)
); 

insert into [my_table] 
values (1, 'Brian', '123-456-7890');

create table [my_log]
(
     [inserted_id]      int
    ,[inserted_name]    nvarchar(256)
    ,[inserted_phone]   nvarchar(256)
    ,[deleted_id]       int
    ,[deleted_name]     nvarchar(256)
    ,[deleted_phone]    nvarchar(256)   
    ,[uid]              uniqueidentifier primary key    
);

And then this solves your problem:

update [my_table]
set [id]     = 2        
    ,[name]  = 'James'
    ,[phone] = '222-222-2222'
output
     [deleted].[id]
    ,[deleted].[name]
    ,[deleted].[phone]
    ,[inserted].[id]
    ,[inserted].[name]
    ,[inserted].[phone] 
    ,newid()
into [my_log]
(
     [inserted_id]
    ,[inserted_name]
    ,[inserted_phone]   
    ,[deleted_id]
    ,[deleted_name]
    ,[deleted_phone]    
    ,[uid]
);

select
     [id]       =   [id]
    ,[name]     =   [name]
    ,[phone]    =   [phone]
    ,[op_type]  =   CASE [op_type] WHEN 0 THEN 'Insert' ELSE 'Delete' END
from    
    ( 
        select 
             [id]       =   [inserted_id]
            ,[name]     =   [inserted_name]
            ,[phone]    =   [inserted_phone]
            ,[op_type]  =   0   --  insert
            ,[uid]      =   [uid]
        from 
            [my_log] 
        union
        select 
             [id]       =   [deleted_id]
            ,[name]     =   [deleted_name]
            ,[phone]    =   [deleted_phone]
            ,[op_type]  =   1   --  delete
            ,[uid]      =   [uid]
        from 
            [my_log]
    ) as [l]
order by 
     [l].[uid] 
    ,[l].[op_type]  ASC;

edited Jan 16, 2017 at 19:40

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 16, 2017 at 18:57

Juozas

94310 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abayomi Over a year ago

Thanks @Juozas, this is a more elaborate answer and it worked just fine. I am unable to mark as answer because I am new to quora so it won't allow me. But thanks a lot!

Juozas Over a year ago

Thank you for a interesting question!

Solomon Rutzky · Accepted Answer · 2017-01-16 18:23:16Z

Unless you are using the MERGE statement, you cannot both INSERT and DELETE in the same DML operation. The only time (outside of MERGE) that you will have rows in both the inserted and deleted pseudo-tables is during an UPDATE operation, and in that case, the rows in both will refer to the same actual rows in the tables, just the before and after views of those rows. Meaning, you won't see values like are what is being shown in the question where Inserted_ID and Deleted_ID differ.
If you are using MERGE, then you should already have the rows in the desired format since one row from the OUTPUT clause wouldn't represent multiple rows being affected. Meaning: OUTPUT already works are you are wanting it to, not as you are stating that it does.

Bogdan Sahlean · Accepted Answer · 2017-01-16 18:26:46Z

0

Try to use OUTER / CROSS APPLY operator:

select ...
from #Output / @Output as o
cross apply (
    select o.insertedColA, o.insertedColB, 'I'
    -- where o.insertedID is not null
    union all
    select o.deletedColB, ..., 'D'
    -- where o.deletedID is not null
) as x(ColA, ColB, RowType)

answered Jan 16, 2017 at 18:26

Bogdan Sahlean

1

Collectives™ on Stack Overflow

SQL Server : splitting a row into multiple rows from OUTPUT clause

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related