September 14, 2013 at 12:16 pm
Hi,
I have an interesting problem that i haven't been able to find a good solution for.
What i want to do is to make an insert from table source into destination and find out the mapping between the source and destination table rows.
The problem is that I cannot match the non-identity values on each other because they're no uniqueness between them. In real world, it's actually adress logging tables.
Here are the sample script:
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
-- Import some sample data...
INSERT INTO [source] (some_value)
SELECT TOP 30 name
FROM sysobjects
-- Destination has some data already
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
I cannot change table definition, and i'd rather not mess with SET IDENTITY_INSERT ON either...
Does someone have an idea?
September 25, 2013 at 11:49 am
In case someone is interested, i solved it by using SET IDENTITY_INSERT ON anyway, there was no other good way...
September 13, 2020 at 11:55 am
A bit silly to reply to own post, but a better solution to this was to use the MERGE clause, it has access to both source and target and inserted / deleted data, allowing this mapping to be achieved without problems.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply