Multiple OUTPUT clauses

Question

Post reply

Multiple OUTPUT clauses

N_Muller

SSCarpal Tunnel

Points: 4037

June 4, 2019 at 4:35 pm

#3650165

Is it possible to create a delete statement with multiple output clauses? Here's the sample code I wish it worked:

create table #t ( id int, x int )
insert #t ( id, x ) values ( 1,1 ), ( 2, 2 ), ( 3, 5 )

create table #o1 ( id int )
create table #o2 ( id int )

;with id as
(
select id = 1
union
select id = 2
)
delete t
output deleted.id into #o1 ( id )
output deleted.x into #o2 ( id )
from id inner join #t t on t.id = id.id

Viewing 3 posts - 1 through 2 (of 2 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

I don't think so.

Can you describe the reasons you think that this would be helpful to you?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76985 More actions · Answer 2

It's all in the documentation.

DELETE (Transact-SQL)

-- Syntax for SQL Server and Azure SQL Database  
  
[ WITH <common_table_expression> [ ,...n ] ]  
DELETE   
    [ TOP ( expression ) [ PERCENT ] ]   
    [ FROM ]   
    { { table_alias  
      | <object>   
      | rowset_function_limited   
      [ WITH ( table_hint_limited [ ...n ] ) ] }   
      | @table_variable  
    }  
    [ <OUTPUT Clause> ]  
    [ FROM table_source [ ,...n ] ]   
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                   { { [ GLOBAL ] cursor_name }   
                       | cursor_variable_name   
                   }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <Query Hint> [ ,...n ] ) ]   
[; ]  
  
<object> ::=  
{   
    [ server_name.database_name.schema_name.   
      | database_name. [ schema_name ] .   
      | schema_name.  
    ]  
    table_or_view_name   
}

It also refers to the OUTPUT Clause (Transact-SQL).

<OUTPUT_CLAUSE> ::=  
{  
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]  
    [ OUTPUT <dml_select_list> ]  
}  
<dml_select_list> ::=  
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]  
    [ ,...n ]  
  
<column_name> ::=  
{ DELETED | INSERTED | from_table_name } . { * | column_name }  
    | $action

Neither definition shows the [ , ...n ] that would indicate that you can have multiple instances, so you can have exactly one instance.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA