Remove duplicates and fix foreign keys in T-SQL

I have the following tables:

Foo_1
Id | Created    | Dupe
 1 | 2019-01-01 | 'one'
 2 | 2019-01-03 | 'one'
 3 | 2019-01-01 | 'two'
 4 | 2019-01-02 | 'one'
 5 | 2019-01-02 | 'three'

Bar_1
Id | BarId
 1 |     1
 2 |     1
 3 |     3
 4 |     2
 5 |     4
 6 |     5

Now I need to insert all Rows of Foo_1 into Foo_2 while removing duplicates.

This is done using this code

SELECT [f].[Id],
       [f].[Created],
       [f].[Dupe]
INTO [Foo_2]
FROM (
         SELECT ROW_NUMBER() OVER (PARTITION BY [Foo_1].[Dupe] ORDER BY [Foo].[Created] DESC) AS [ROW_NUMBER],
                [Foo_1].[Id],
                [Foo_1].[Created],
                [Foo_1].[Dupe],
     ) AS [f]
WHERE [f].[ROW_NUMBER] = 1;

Now I have

Foo_2
Id | Created    | Dupe
 2 | 2019-01-03 | 'one'
 3 | 2019-01-01 | 'two'
 5 | 2019-01-02 | 'three'

And now I need to create the table Bar_2 which should look like the following

Bar_1
Id | BarId
 1 |     2
 2 |     2
 3 |     3
 4 |     2
 5 |     2
 6 |     5

But how can I find out the correct new Foreign Key value for BarId?

Answer

You should be able to match Bar_1.BarId to Foo_1.Id to get the Foo_1.Dupe to match Foo_2.Dupe to get the Foo_2.Id.

select b.Id, b.BarId oldBarId, f2.Id newBarId
from Bar_1 b
join Foo_1 f1 on f1.Id = b.BarId
join Foo_2 f2 on f2.Dupe = f1.Dupe

Advertisement

Answer