Insert - Select keeping identity mapping

Question

I have 2 tables, and im trying to insert data from one to another and keepeng the mappings between ids. I found here someone with the same problem, but the solution isnt good for me.

here is the example:

the two tables

CREATE TABLE [source] (i INT identity PRIMARY KEY, some_value VARCHAR(30))
CREATE TABLE [destination] (i INT identity PRIMARY KEY, some_value VARCHAR(30))

CREATE TABLE [mapping] (i_old INT, i_new INT) -- i_old is source.i value, i_new is the inserted destination.i column

some sample data

INSERT INTO [source] (some_value)
SELECT TOP 30 name
FROM sysobjects

INSERT INTO [destination] (some_value)
SELECT TOP 30 name
FROM sysobjects

Here, i want to transfer everything from source into destination, but be able to keep a mapping on the two tables: I try to use OUTPUT clause, but i cannot refer to columns outside of the ones being inserted:

INSERT INTO [destination] (some_value)
--OUTPUT inserted.i, s.i INTO [mapping] (i_new, i_old) --s.i doesn't work
SELECT some_value
FROM [source] s

Anyone has a solution for this?

this isnt the generic behavior, so im staying away from triggers. Cursor is an option, but.. it is the only besides triggers? — BFigueiredo, Apr 16 '14 at 14:02
do you have permission to modify the table? to add 1 more column, the source_Id in the destination table. it will solve your problem. — cyan, Apr 16 '14 at 14:07
Sorry i don't quite undestand. In the example you post source and destination have the same data. Do you want to insert in destination records that are in source but not is destination by ID? — ericpap, Apr 16 '14 at 14:09
ericpap the data is just an example. Cyan, now i have permissions, but im not sure if the app will keep the permissions when deployed, but most probably will :) — BFigueiredo, Apr 16 '14 at 14:14
Ok. So as @cyan the only way is to keep a reference on destination of the source ID in a new column. — ericpap, Apr 16 '14 at 14:17
run the alter sql now :). after that you can insert the record to the mapping table with ease forever. — cyan, Apr 16 '14 at 14:25

cyan · Answer 1 · 2014-04-16T15:27:34.617

try this sql below if you don't have permission to modify the tables: The idea is using a temp table to be a bridge between destination table and the mapping table.

SQL Query:

declare @source table (i INT identity PRIMARY KEY, some_value VARCHAR(30))
declare @destination table (i INT identity PRIMARY KEY, some_value VARCHAR(30))
declare @mapping table (i_old INT, i_new INT) -- i_old is source.i value, i_new is the inserted destination.i column


declare @tempSource table
(

 id_source INT identity , source_value VARCHAR(30)
 ,Id_New int,source_new VARCHAR(30)
)


insert into @source
output inserted.i, inserted.some_value into @tempSource(id_source,source_value)
SELECT TOP 10 name
FROM sysobjects


--select * from @tempsource


insert into @destination
OUTPUT inserted.i, inserted.some_value  INTO @tempSource (Id_New,source_new)
select source_value from @tempSource


insert into @mapping
select Id_source, Id_New from 
(
    select a.id_source, a.source_value
    from
     @tempSource a
    where id_source is not null and source_value is not null
) aa
inner join 
(
    select a.Id_New, a.source_new
    from
     @tempSource a
    where Id_New is not null and source_new is not null
) bb on aa.source_value = bb.source_new


select * from @mapping

The mapping table result:

i_old       i_new
----------- -----------
1           1
2           2
3           3
4           4
5           5
6           6
7           7
8           8
9           9
10          10

score 1 · Accepted Answer · answered Apr 16 '14 at 14:53

1

Not sure is it write way but it works :D

MERGE [#destination] AS D
USING [#source] AS s
    ON s.i <> s.i
WHEN NOT MATCHED BY TARGET
THEN 
    INSERT (some_value) VALUES (some_value)
OUTPUT inserted.i, s.i INTO [#mapping] (i_new, i_old);

answered Apr 16 '14 at 14:53

Darka

2,762
1
14
31

Insert - Select keeping identity mapping

2 Answers2