CODEWITHSUNDEEP
sqlsql-server-2008selectjoin
Snowy
Snowy

Reputation: 6132

SQL Server Select After Merge Join on Column

Using a populated Table Type as the source for a TSQL-Merge. I want to execute a select statement after the merge, retrieving all columns/rows of the table type, but instead of the '-1' value, I want the new inserted id. I am not sure I can do this in a fully set-based manner, can I?

This is for a UI that is sending a pile of inserts into the DB, and needs back the same objects, but with each of their ID column values populated. There is no 'common column' for the SQL JOIN operation.

CREATE TYPE instype AS TABLE(
    instypeid [smallint] NOT NULL,
    instext [varchar](64) NOT NULL
)
Go
create table #desttable ( instypeid smallint identity(1,1) primary key , instext varchar(64) )
Go
declare @newids table ( idvalue smallint )
declare @thing1 instype
insert into @thing1 values ( -1 , 'zero' )
insert into @thing1 values ( -1 , 'one' )
    Merge #desttable desttbl
            Using @thing1  srctbl
            On desttbl.instypeid = srctbl.instypeid
            When Not Matched Then
                Insert ( instext )
                Values ( instext )
            Output inserted.instypeid Into @newids
        ;

/*
        Wanted shape of the result set
        instypeid   instext
        0           zero
        1           one

*/

Thanks.

Upvotes: 2

Views: 522

Answers (1)

Ivan Golović
Ivan Golović

Reputation: 8832

But you can get that result set by slightly modifying your current code:

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

create table #desttable ( instypeid smallint identity(0,1) primary key
, instext varchar(64) )
Go
declare @inserted table ( idvalue smallint, instext varchar(64) )
declare @thing1 instype

insert into @thing1 values ( -1 , 'zero' ), ( -1 , 'one' )

Merge #desttable desttbl
        Using @thing1  srctbl
        On desttbl.instypeid = srctbl.instypeid
        When Not Matched Then
            Insert ( instext )
            Values ( instext )
        Output inserted.instypeid, inserted.instext Into @inserted
    ;

SELECT  *
FROM    @inserted

Upvotes: 4

Related Questions