AnishaJain AnishaJain - 3 months ago 7
SQL Question

How to insert source columnvalue with the insertion output into a new table?

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

Answer

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
Comments