declare @Friend table( ID1 int, ID2 int ) 

insert @Friend
select 2, 1
union all
select 2, 3
union all
select 4, 1
union all
select 5, 6


-- 1. Приводим @Friend к ID1 < ID2 

update @Friend set id1 = id2
                  ,id2 = id1
where id1>id2;

select * from @friend;

-- 2. ваяем пары
;with
-- все пары "A is friends with B, and B is friends with C, add a new friendship for the pair A and C"
--, за вычетом "friendships with oneself"
allpairs as (
	select F1.id2 as id1, F2.id2 as id2
	from @Friend F1 inner join @Friend F2 on F1.id1=F2.id1
	where F1.id2 <> F2.id2 
	union
	select F1.id2 as id1, F2.id1 as id2
	from @Friend F1 inner join @Friend F2 on F1.id1=F2.id2
	where F1.id2 <> F2.id1 
	union
	select F1.id1 as id1, F2.id2 as id2
	from @Friend F1 inner join @Friend F2 on F1.id2=F2.id1
	where F1.id1 <> F2.id2 
	union
	select F1.id1 as id1, F2.id1 as id2
	from @Friend F1 inner join @Friend F2 on F1.id2=F2.id2
	where F1.id1 <> F2.id1 
)
--select * from allpairs;
,
-- нормализованные пары, "id1 < id2" без повторов
normalpairs as
(select distinct
        (select min(id1) from (select id1 union all select id2) X) as id1
      , (select max(id1) from (select id1 union all select id2) X) as id2
   from allpairs)
--select * from normalpairs;
,
-- не существующие пары
nonexistingpairs as
( select * from normalpairs
  except
  select * from @Friend
)
-- ну и фсе
insert @Friend
select * from nonexistingpairs;

select * from @friend;

declare @f table(id1 int, id2 int, primary key (id1, id2));

insert into @f
values
 (1, 2), (2, 1), (1, 3), (3, 1), (2, 3), (3, 2), (3, 4), (4, 3), (4, 5), (5, 4);

insert into @f
select distinct
  a.id1, c.id1
from
 @f a join
 @f b on b.id1 = a.id2 join
 @f c on c.id1 = b.id2
where
 a.id1 <> c.id1 and
 not exists(select 1 from @f where id1 = a.id1 and id2 = c.id1);

select * from @f;

declare @f table(id1 int, id2 int, primary key (id1, id2));

insert into @f
values
 (1, 2), (2, 1), (1, 3), (3, 1), (2, 3), (3, 2), (3, 4), (4, 3), (4, 5), (5, 4);

insert into @f
select a.id1, b.id2 from @f a join @f b on b.id1 = a.id2 where  a.id1 <> b.id2
except 
select * from @f ;

select * from @f;

insert into Friend (ID1, ID2)
select distinct a.ID1, c.ID2
from Friend a
join Friend c on a.ID2 = c.ID1
where not exists ( select * from Friend ex 
                         where ex.ID1 = a.ID1 and ex.ID2 = c.ID2 )
     and a.ID1 <> c.ID2