2

I'm currently working on some kind of data mapping. Lets say I have the following three tables:

TemporaryTable 

RUNID | DocId | Amount
E       7       50
C       6       12

Table1 

T1ID | DocID | Amount
1      5       10
2      6       20
3      6       50

Table2 

T2ID | RUNID | T1Id
1      B       1
2      C       2
3      D       3

In table Table1 and Table2 the columns T1ID and T2ID are identity columns that are populated automatically. What I want to do now is to insert the values from TemporaryTable into Table1 and save the value in column RunID from TemporaryTable and the newly generated T1ID to Table2

The resulting table should look like this:

Table1 

T1ID | DocID | Amount
1      5       10
2      6       20
3      6       50
4      7       50
5      6       12

Table2 

T2ID | RUNID | T1Id
1      B       1
2      C       2
3      D       3
4      E       4
5      C       5

I would like to do so with the help of the output statement. Something like this:

CREATE TABLE #map(T1ID, RUNID)
INSERT INTO Table1(DocId, Amount)
OUTPUT inserted.T1ID, t.RunId INTO #map 
SELECT t.DocId, t.Amount
FROM TemporaryTable t

This obviously doesn't work since I have no access to t.RunId in the output statement. How could this be done?

Romano Zumbé
  • 7,893
  • 4
  • 33
  • 55

1 Answers1

5

You can use MERGE command with some always-false condition to simulate your insert with all columns available in OUTPUT

MERGE Table1 t1
USING TemporaryTable t
  ON 1=2
WHEN NOT MATCHED THEN
  INSERT (DocId, Amount)
  VALUES (t.DocId, t.Amount)
  OUTPUT inserted.T1ID, t.RunId 
  INTO #map ;
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55