2

This question is really two parts. The first part is just inserting into a real table with a temp table and having the output inserted into another temp table is not working. Here is the example.

Insert Into student 
        Output INSERTED.* 
        Into #StudentsOutput
Select *
From #StudentsResult

The error from above when I hover over it.

Invalid Object name '#StudentsOutput'

All msdn examples I saw could do this, unless because it involves two temp tables you cannot do this.

Also note that I tried stating the columns specifically for the student table, and from the #StudentsResult table. This yielded the same results from above. So for brevity I am only showing the above tsql

The next issue is I would like the new temp table #StudentsOutput to contain the old student id. Since I am going to modify other tables that need the reference to the old student id. Example below.

Insert Into student 
    Output INSERTED.student_id, s.student_id as [old_student_id]  
    Into #StudentsOutput
Select *
From #StudentsResult as s

This would be perfect for what I need a lookup of the old value to the new value! Error below.

The multi-part identifier 's.student_id' could not be bound.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
christopher clark
  • 2,026
  • 5
  • 28
  • 47
  • For the second Issue, you can't do it using `insert`. You need to use `merge`. See [this](https://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage/34832231#34832231) answer and [this](https://stackoverflow.com/questions/41184310/insert-into-merge-select-sql-server/41184461#41184461) one as well. – Zohar Peled Aug 17 '17 at 15:23

1 Answers1

3

First issue (you have to create both temp tables):

CREATE TABLE student(id INT);
CREATE TABLE #StudentsResult(id INT);
CREATE TABLE #StudentsOutput(id INT);


 Insert Into student 
        Output INSERTED.* 
        Into #StudentsOutput
 Select *
 From #StudentsResult;

RextesterDemo

From OUTPUT:

output_table

Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

Note that table should exist before you try to insert. I suppose you wanted that it works like here:

SELECT *
INTO #temp_table     -- in this scenario table will be created automatically
FROM ...

Second issue. You don't need aliases in output clause. Just specify column list INTO:

Insert Into student 
        Output INSERTED.student_id, INSERTED.col_name 
        Into #StudentsOutput(col1, col2)
Select *
From #StudentsResult as s;

Then 

INSERTED.student_id -> col1
INSERTED.col_name   -> col2

Please note that you could only refer to columns from INSERTED pseudotable.

If you need to refer also source you have to switch to MERGE statement.

 MERGE student trg
 USING @StudentResult src
   ON ...
 WHEN NOT MATCHED BY TARGET THEN 
    INSERT ...
 OUTPUT inserted.student_id, src.student_id
 INTO #StudentsOutput(col1, col2)
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    AFAIK, you can't use columns from both source and target in the output clause of an insert statement, only from the [output clause of a merge statement.](https://stackoverflow.com/questions/41184310/insert-into-merge-select-sql-server/41184461#41184461) – Zohar Peled Aug 17 '17 at 15:24
  • at the worst case scenario. I can do an inner join of the `#StudentsResult` and `#StudentsOutput` on a `rank() row over partition...` since the two tables should be identical in a row. But that just seems dirty. – christopher clark Aug 17 '17 at 15:30
  • It seems from your edit I will need to do use a row over partition from the Result and Output temp table. A merge wouldn't work in this case. – christopher clark Aug 17 '17 at 15:31
  • @christopherclark I think that your main question has been solved. I propose to ask new question based on this one, prepare input data and desired resultset. **[Rextester demo](http://rextester.com)** would help a lot. – Lukasz Szozda Aug 17 '17 at 15:37