sql

Create TABLE linktable(
[src_device_id] integer
,[src_port_number] integer
,[des_device_id] integer
,[des_port_number] integer
);


alter table linktable add id int identity


insert into linktable(src_device_id, src_port_number, des_device_id, des_port_number)
select 1,1,2,1
union
select 2,1,3,1
union
select 1,1,5,2
union
select 3,1,4,1
select * from linktable


select device_id, port into #t1  from(select id, src_device_id as device_id, src_port_number as port From linktable where src_device_id != des_device_id
union
Select id, des_device_id as device_id, des_port_number as port From linktable where src_device_id != des_device_id)
as lk1 group by device_id, port having count(*) > 1


create table cloud(device_id int,prot_id int,cloud varchar(10))


select * from cloud


select * from #t1
device_id,prot_id,cloud
1,1,1_1
2,1,1_1
select top 1* from  where $src_device_id=device_id and $src_port_number = port) or ($des_device_id=device_id and )




select * from linktable
set src_dev=1,src_port=1,des_devid=2,des_prot=1
set src_cloud=select top 1 cloud from cloud where device_id=$src_dev and 
set des_cloud=select top 1 cloud from cloud where device_id=$des_dev and 
if(src_colud !=null and desc_colod !=null )
    if(src_colord != desc_colod)
         update cloud 
         
else if(src_colud ==null and desc_colod ==null)
  select top 1 device,port from  t1 where $src_device_id=device_id and $src_port_number = port) or ($des_device_id=device_id and )
  if(!=null)
  insert cloed(src,pot,clould)value(src_dev=1,src_port=1,device_port)
  insert cloed(src,pot,clould)value(des_dev=1,des_port=1,device_port)


else if(src_cloud==null)
   insert cloed(src,pot,clould)value(src_dev=1,src_port=1,des_cloud)
else if(des_cloud==null)

  insert cloed(src,pot,clould)value(des_dev=1,des_port=1,src_cloud)

linktable
1 1 2 1 1
1 1 5 2 2
2 1 3 1 3
3 1 4 1 4


#t1
1 1
2 1
3 1


cloud
1,1,1_1
2,1,1_1
5,2.1_1
3,1,1_1
4,1,1_1