title: TSQL Merge 2 Ids into Temp Table
body: <p>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 <code>Projects</code> and want to store the <code>inserted.ProjectId</code> together with <code>oID</code> from the importing table into a temporary Table; I don't store the <code>oID</code> from the importing table into the <code>Projects</code> table.</p>
  
  <p>I'm referring myself to this answer:
  <a href="https://stackoverflow.com/questions/41184310/insert-into-merge-select-sql-server">Insert Into... Merge... Select (SQL Server)</a></p>
  
  <p>1) <strong>Importing Table:</strong> I'm sending a table from C# to SQL with the following values:</p>
  
  <pre><code>oID |  Title   | 
  ----+----------+
   0  | ProjectX |
   1  | ProjectY |
   2  | ProjectZ |
  </code></pre>
  
  <p>It looks like this in the stored procedure:</p>
  
  <pre><code>CREATE TYPE [MySchema].[Project] AS TABLE
  (
      oID INT,
      Title VARCHAR(100)
  );
  
  @ImportProjects MySchema.Project READONLY
  </code></pre>
  
  <p>2) <strong>Projects Table:</strong> 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:</p>
  
  <pre><code>ProjectId |  Title   | 
  ----------+----------+
  33        | ProjectX |
  34        | ProjectY |
  35        | ProjectZ |
  </code></pre>
  
  <p>3) <strong>Desired Output, Temporary Table</strong>: In my temporary table I want to store the ProjectId as well as the oID from the importing table:</p>
  
  <pre><code> RowID  | oID |  ProjectId   | 
  --------+-----+--------------+
  1       |  0  | 33           |
  2       |  1  | 34           |
  3       |  2  | 35           |
  </code></pre>
  
  <p>My Merge query looks like this:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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...</p>
  
  <p>Do you know how to achieve this?</p>
  
  <p>Thank you !! :)</p>