I have a set of FakeIds, which are then converted to real ids on insertion. I now want to obtain the mapping of fake to real. My query is like
INSERT INTO Data_tbl
(days)
OUTPUT inserted.DateID, 0,source.DateID into @mappedIDs(objectId, objectTypeId, fakeId)
SELECT Days
FROM @inputTable
I know that Source.DateId won't work but I have put it there to so explain what I need
USE a MERGE command instead... in the OUTPUT clause you can mention the inserted and source column that you required.
Sample code is given below.
MERGE Data_tbl d
USING ( SELECT DateID,Days
FROM @inputTable
) d1 ON d.DateID = d1.DateID
WHEN NOT MATCHED
THEN INSERT
([days])
VALUES (d1.[days])
OUTPUT inserted.DateID, 0,d1.DateID INTO @mappedID
The trick to mapping inserted data to source data when you don't pass both IDs to the destination table is tricky, you have to build your own map. Here's my full script you can use to build your own.
First, to emulate your destination table I created a temp table, so I always put a check to see if the temp table already exists, in case I want to run the demo multiple times.
IF OBJECT_ID('tempdb..#Data_tbl') IS NOT NULL
DROP TABLE #Data_tbl
Then I create my version of your destination table with just the two columns I need.
CREATE TABLE #Data_tbl (
DateID INT IDENTITY(1,1)
, [Days] DATE
)
Then I create my table variable like you did, but I changed the column names to hopefully make things less complicated. In my output, I'm only capturing the ID created, and the day that was inserted. The day inserted is important, because that will be my key I use in my join later.
DECLARE @mappedIDs TABLE (
InsertedID INT
, [Days] DATE
)
Then I set up a table representing my rows to be inserted. That way you can test what happens when you insert more than one row in a batch!
DECLARE @inputTable TABLE (
FakeID INT
, [Days] DATE
)
Now, let's stuff a row into the source.
INSERT INTO @inputTable
VALUES (1, '9/2/2016')
Here's the insert statement. Notice I'm only inserting the days in this example, but you could insert more if you needed to. The output clause there is the important thing to keep the same if you're trying to build your map. You've got to collect the DateID created in your destination table, and the Days.
INSERT INTO #Data_tbl
([Days])
OUTPUT inserted.DateID, inserted.[Days] into @mappedIDs(InsertedID, [Days])
SELECT Days
FROM @inputTable source
Now, let's show the map! I take the inserted rows (in @mappedIDs) and join it to my source records on Days. Now I can see the mapping from FakeID to "real" ID.
SELECT
results.InsertedID, src.FakeID, src.[Days]
FROM @mappedIDs results
INNER JOIN @inputTable src
ON results.[Days] = src.[Days]
daysunique? If so, that could be used to join the source and target after the insert to determine the mapping.