Insert into each column of a table values based on conditions

Question

I have a table of products like this:

I want to delete duplicate rows in this table and use the Ids in other tables, so I used a temporary table to add just the Ids to delete and the Ids to keep:

-- create tmp table
create table #tmp (ProductId_ToKeep int, ProductId_ToDelete int);

-- collect the Products that have a lower id with the same name in the temp table
insert into #tmp (ProductId_ToKeep)
select [ProductId]
from dbo.[Product] t1
where exists
(
    select 1
    from dbo.[Product] t2
    where t2.name = t1.name
      and t2.[ProductId] > t1.[ProductId]
);
-- collect the Products that have a higher id with the same name in the temp table
insert into #tmp (ProductId_ToDelete)
select [ProductId]
from dbo.[Product] t1
where exists
(
    select 1
    from dbo.[Product] t2
    where t2.name = t1.name
      and t2.[ProductId] < t1.[ProductId]
);
select * from #tmp

After getting what I have in my temp table, I got this result:

I'm asking if any can one help me to put the Ids in each column as I want.

Answer 1

If I followed you correctly, you could use a window function to feed the transcodification table in a single query, like so:

insert into #tmp (ProductId_ToKeep, ProductId_ToDelete)
select *
from (
    select 
        ProductId ProductId_ToDelete, 
        min(ProductId) over(partition by name) ProductId_ToKeep
    from dbo.[Product]
) t
where ProductId_ToDelete != ProductId_ToKeep

The inner query pulls out the smallest ProductId for the given name; the outer query filters on record that should be deleted (ie whose ProductId is not the minimum ProductId for the same name).