TSQL Merge 2 Ids into Temp Table

Question

I'm working on a stored procedure in TSQL on SQL Server 2012. When I call the stored procedure. My problem: I'm inserting the values into a table Projects and want to store the inserted.ProjectId together with oID from the importing table into a temporary Table; I don't store the oID from the importing table into the Projects table.

I'm referring myself to this answer: Insert Into... Merge... Select (SQL Server)

1) Importing Table: I'm sending a table from C# to SQL with the following values:

oID |  Title   | 
----+----------+
 0  | ProjectX |
 1  | ProjectY |
 2  | ProjectZ |

It looks like this in the stored procedure:

CREATE TYPE [MySchema].[Project] AS TABLE
(
    oID INT,
    Title VARCHAR(100)
);

@ImportProjects MySchema.Project READONLY

2) Projects Table: I'm inserting the values into the Table Projects, and want to store the inserted.ProjectId as well as the oID from the importing Table into a temporary table:

ProjectId |  Title   | 
----------+----------+
33        | ProjectX |
34        | ProjectY |
35        | ProjectZ |

3) Desired Output, Temporary Table: In my temporary table I want to store the ProjectId as well as the oID from the importing table:

 RowID  | oID |  ProjectId   | 
--------+-----+--------------+
1       |  0  | 33           |
2       |  1  | 34           |
3       |  2  | 35           |

My Merge query looks like this:

create table Temp (ProjectId INT, oID INT)

MERGE INTO Temp USING
(
    SELECT 
        a.oID as oID,
        b.ProjectId as ProjectId
    FROM @ImportProjects a
    CROSS JOIN Projects b
) AS s ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (oID, ProjectId)
VALUES (s.oID, s.ProjectId)
OUTPUT Inserted.ProjectId, s.oID
INTO Temp(oID, ProjectId);

This doesn't really work... One of my problems is of course the cross join, but i dont have a link or foreign key inbetween importing table and projects table...

Do you know how to achieve this?

Thank you !! :)

Is there a set of columns in your `Project` table that uniquely identify a row other than `ProjectId` ? — SqlZim, Apr 27 '17 at 15:41
In my Projects Table I have only the primary key ProjectId ProjectId INT IDENTITY(1,1) — TimHorton, Apr 27 '17 at 15:44

score 1 · Answer 1 · answered Apr 27 '17 at 15:37

1

replace CROSS JOIN with INNER JOIN. Foreign key is not requered for inner join

create table Temp (ProjectId INT, oID INT)

MERGE INTO Temp USING
(
    SELECT 
        a.oID as oID,
        b.ProjectId as ProjectId
    FROM @ImportProjects a
    INNER JOIN JOIN Projects b ON a.Title = b.Title
) AS s ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (oID, ProjectId)
VALUES (s.oID, s.ProjectId)
OUTPUT Inserted.ProjectId, s.oID
INTO Temp(oID, ProjectId);

answered Apr 27 '17 at 15:37

Mikhail Lobanov

2,976
9
24

thanks Mikhail, but it seems not to work unfortunately, it produces 6 rows instead of 3... hmm – TimHorton Apr 27 '17 at 15:45
1

It's because of merge. 3 rows inserted by `WHEN NOT MATCHED` statement, another 3 rows by `OUTPUT INTO` statement. Use simple `INSERT INTO` or insert output into another table – Mikhail Lobanov Apr 27 '17 at 15:51

score 1 · Accepted Answer · answered Apr 27 '17 at 16:08

Your merge attempt is a little confusing, but I think this is what you want instead:

create table tvp (oid int, Title varchar(100));
insert into tvp values (0,'ProjectX'),(1,'ProjectY'),(2,'ProjectZ');

create table #tmp (
    Id int not null identity(1,1)
  , oid int
  , ProjectId int
  , Title varchar(100)
);
create table Project (
    ProjectId int not null identity(33,1)
  , Title varchar(100)
);

merge into Project as Target
using tvp as Source
  on 1 = 0
when not matched then
insert (Title)
  values (source.Title)
output source.oid, Inserted.ProjectId, Inserted.Title
into #tmp;

select * from #tmp;

rextester demo: http://rextester.com/PIRFA34601

returns:

+-----+-----+-----------+----------+
|  Id | oid | ProjectId |  Title   |
+-----+-----+-----------+----------+
|   1 |   0 |        33 | ProjectX |
|   2 |   1 |        34 | ProjectY |
|   3 |   2 |        35 | ProjectZ |
+-----+-----+-----------+----------+

Rextester does not support table types (because they can not be declared and used in the same transaction), so I used the table tvp instead.

cool, many thanks for this detailed example! ...and the real advantage is that i don't have to join on the title! :) — TimHorton, Apr 27 '17 at 16:27

TSQL Merge 2 Ids into Temp Table

2 Answers2