树型菜单表的合并。

环境需求：

两个Menu表，结构一样，数据不同，需要把  PM_Menu 数据导入到 Menu表中， 其中 Menu 表结构为： 

CREATE TABLE [dbo].[Menu](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PID] [int] NULL,
[Text] [varchar](50) NULL,
[Icon] [varchar](50) NULL,
[Url] [varchar](250) NULL,
[AddTime] [date] NULL,
[Status] [varchar](50) NULL,
[Wbs] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[AddUser] [varchar](50) NULL,
[SortID] [float] NULL,
CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

其中 Wbs 码是 从根节点到父节点的路径，用 “，” 分隔。

 

解决大纲

1. 把要导入的数据放到临时表，进行数据加工。 把ID，PID， 加工为正确的值。 插入到 Menu

2. 更新Wbs

 

1.导入数据

select cast(ID as int) as IID , * into #t from Pm_Menu
select * from #t
alter table #t  drop column ID    ;
 
go 
 
declare @curVal as int ;
select @curVal =  ident_current('Menu') ;


update  #t set IID = IID + @curVal , PID = PID + @curVal

update #t set PID = 0 where PID = @curVal ;
update #t set Name = 'Pm';

set identity_insert Menu on

insert into Menu (ID, PID, Text, Icon, Url, AddTime, Status, Wbs, Name, AddUser, SortID) select * from #t;

set identity_insert Menu off
 
go
drop table PM_Menu ;
go

 

2. 更新Wbs

用以下方法确定级别：

with m as ( 
select 1 as lvl,* 
from Menu 
where PID =  0
union all
select m.lvl +1 as lvl, s.* 
from Menu as s
join m on ( s.pid = m.id)  
)

 

先更新第一级：

update Menu set Wbs = '0' where PID = 0 ;

 

再逐级更新第二级，第三级

 

with m as ( 
select 1 as lvl,* 
from Menu 
where PID =  0
union all
select m.lvl +1 as lvl, s.* 
from Menu as s
join m on ( s.pid = m.id)  
)


update Menu
set menu.Wbs = '0,' + CAST(menu.PID as varchar(30))
from Menu  
join m on (menu.ID = m.id )
where  m.lvl = 2

 

逐级 改变一下 m.lvl 的值即可。

3.一步更新Wbs

做一个循环实现所有的级别， 完整代码如下： 

declare @m table( lvl int , id int  ) ;


    with m as ( 
    select 1 as lvl,* 
    from Menu 
    where PID =  0
    union all
    select m.lvl +1 as lvl, s.* 
    from Menu as s
    join m on ( s.pid = m.id)  
    )
    insert into @m select lvl , id  from m ;

update Menu set Wbs = '0' where PID = 0 ;

declare @lvl int ;
set @lvl = 1

while @lvl < 12 begin
    set @lvl = @lvl +1 ;
    
    update c
    set c.Wbs = p.Wbs +',' + CAST(c.PID as varchar(30))
    from Menu as c  
    join Menu as p on (c.PID = p.id )
    where  c.ID in ( select ID from @m where lvl = @lvl ) ;
end;

 

 4. 一步更新 S_City

使用临时表，更方便。

with m as ( 
    select 1 as lvl,* 
    from S_City 
    where PID =  0
    union all
    select m.lvl +1 as lvl, s.* 
    from S_City as s
    join m on ( s.pid = m.id)  
)
select lvl , id  into #t from m ;

update S_City set Wbs = '0' where PID = 0 ;

declare @lvl int ;
set @lvl = 1

while @lvl < 6 begin
    set @lvl = @lvl +1 ;
    
    update c
    set c.Wbs = p.Wbs +',' + CAST(c.PID as varchar(30))
    from S_City as c  
    join S_City as p on (c.PID = p.id )
    where  c.ID in ( select ID from #t where lvl = @lvl ) ;
end;