October 27, 2014 at 9:23 am
Hello all,
We are importing data (parent/childrows) from another database. Problem as always is the identity. They are not the same on both system.
When a row is not matched (on a logical key) we insert the row and output the old and the new identity into a table.
When matched, we do an update of the row, over all fields, except the identity field. I would like to output the old and the new identity into a table.
The old and new Identities are used to resolve the child rows with the parent rows.
I searched this site and googled. I can not find a solution.
I am looking for a solution.
If this is not possible within the merge please confirm that this is not an option.
Ben
October 27, 2014 at 9:49 am
Looks like OUTPUT is not an issue here.
For instance, this works:
-- original table
DECLARE @originalTable TABLE (
id int,
logicalKey varchar(50)
)
-- some sample data
INSERT INTO @originalTable VALUES
(1, 'Row1'),
(2, 'Row2'),
(3, 'Row3'),
(4, 'Row4'),
(5, 'Row5')
-- the other table
DECLARE @otherTable TABLE (
id int,
logicalKey varchar(50)
)
-- more sample data, some matching, some new
INSERT INTO @otherTable VALUES
(12, 'Row2'),
(4, 'Row4'),
(15, 'Row5'),
(6, 'Row6'),
(7, 'Row7')
-- table to hold old and new id
DECLARE @targetTable TABLE (
old_id int,
new_id int
)
-- merge with output
MERGE INTO @originalTable AS trg
USING @otherTable AS src
ON trg.logicalKey = src.logicalKey
WHEN MATCHED
THEN UPDATE SET trg.id = src.id
WHEN NOT MATCHED
THEN INSERT VALUES(src.id, src.logicalKey)
OUTPUT DELETED.id AS old_id, INSERTED.id AS new_id INTO @targetTable;
-- show results
SELECT *
FROM @targetTable;
Is this what you're after?
-- Gianluca Sartori
October 27, 2014 at 10:16 am
First of all thanks for the example. (Kudos).
I adjusted the example see below. (The identity does not get updated)
When an update is done, all fields are updated exept the identity field.
(The logical key even if it is updated remains the same, because these values match).
Row5 has the identity of 5 in the originalTable.
Row5 has the identity of 15 in the otherTable.
There is also a OtherChildTable which points to Row5, but uses the VALUE 15 to point to the correct row.
When this OtherChildTable gets merged with a similar script the 15 has to be resolved into 5 before the row can be merged.
The targettable contains the resolving for the new rows, not for the updated rows.
To resolve this the tragettable should contain a row with the OLD and the NEW identity for the updated rows.
If this is possible in the merge I can not find it.
If this is not possible in the merge statement, I would like to know then I need some extra statements to do the resolve action.
Thanks again for your example.
Ben
spaghettidba (10/27/2014)
Looks like OUTPUT is not an issue here.For instance, this works:
-- original table
DECLARE @originalTable TABLE (
id int,
logicalKey varchar(50),
datafields varchar(50)
)
-- some sample data
INSERT INTO @originalTable VALUES
(1, 'Row1', 'serveral datafields.'),
(2, 'Row2', 'represented by this.'),
(3, 'Row3', 'single field.'),
(4, 'Row4', 'they caaan be uipdated'), -- intentional mistake in row4
(5, 'Row5', 'or bbbb kept.')
-- the other table
DECLARE @otherTable TABLE (
id int,
logicalKey varchar(50),
datafields varchar(50)
)
-- more sample data, some matching, some new
INSERT INTO @otherTable VALUES
(12, 'Row2', 'represented by this.'),
(4, 'Row4','they can be updated CORRECTED'), -- corrected row4
(15, 'Row5', 'or kept as they are. CORRECTED'), -- changed row 5
(6, 'Row6', 'this is a new rwo.'), -- mistake for the next round.
(7, 'Row7', 'And again a new row')
-- table to hold old and new id
DECLARE @targetTable TABLE (
old_id int,
new_id int
)
-- merge with output
MERGE INTO @originalTable AS trg
USING @otherTable AS src
ON trg.logicalKey = src.logicalKey
WHEN MATCHED
THEN UPDATE SET trg.datafields = src.datafields ----------I want to have the old and new identity for these rows.
WHEN NOT MATCHED
THEN INSERT VALUES(src.id, src.logicalKey, src.datafields)
OUTPUT DELETED.id AS old_id, INSERTED.id AS new_id INTO @targetTable;
select * from @originalTable
-- show results
SELECT *
FROM @targetTable;
-- In the targetable I would like to see The Inserted Identities.
-- In the targetabel I would like to see the Identities for the updated rows.
-- a CHILD of this table is pointing to ROW5, with but uses the VALUE 15 to point to the identity 15.
Is this what you're after?
October 27, 2014 at 10:36 am
This should do the trick:
-- merge with output
MERGE INTO @originalTable AS trg
USING @otherTable AS src
ON trg.logicalKey = src.logicalKey
WHEN MATCHED
THEN UPDATE SET trg.datafields = src.datafields ----------I want to have the old and new identity for these rows.
WHEN NOT MATCHED
THEN INSERT VALUES(src.id, src.logicalKey, src.datafields)
OUTPUT DELETED.id AS old_id, src.id AS new_id INTO @targetTable;
-- Gianluca Sartori
October 28, 2014 at 9:27 am
spaghettidba (10/27/2014)
This should do the trick:
Yes this is what I was after.
Thanks very much for pointing me in the right direction.
I just implemented this within my code and it does exactly what I wanted.
I am a bit suprised that I haven't managed to find this simple and elegant solution using searches on the web.
Thanks for your time and attention.
Ben
October 28, 2014 at 9:40 am
Welcome, glad I could help
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply