将Excel文件中树状结构的数据导入到MSSQL数据库的表中

 

该图为一树状结构的Excel文件的数据

 

首先将该Excel文件导入到数据库

打开数据库，右键数据库名，选择任务里的导入数据（这里不详细解释）

 

导入完成后，表中会有一个跟这一样的表（tb）

然后对此表操作，具体操作如下：

select * from tb

alter table tb add num int
alter table tb add id1 int
alter table tb add id2 int
alter table tb add id3 int
-- 给表加一个编号字段num，以便于更新节点ID
update t set num = a.num from tb t,
(
	select row_number() over (order by getdate()) num,name3 from tb 
) a 
where a.name3 = t.name3

--更新父节点ID
update a set id1 = (select max(b.num) from tb b where a.num >= b.num and isnull(b.name1,'')<> '')
from tb a
--更新子节点ID，首先要查一下最大ID是多少，要避免ID重复
select max(num) from tb

update tb set id2 = num +100

update tb set id3 = id2 +100

--更新子节点ID
update a set id2 = (select max(b.id2) from tb b where a.id2 >= b.id2 and isnull(b.name2,'') <> '')
from tb a

update a set id3 = (select max(b.id3) from tb b where a.id3 >= b.id3 and isnull(b.name3,'') <> '')
from tb a
drop table tb_tree
--创建存放树的表
create table tb_tree(id int,pid int,name nvarchar(100))

--向表中插入根节点
insert into tb_tree
select id1,0,name1 from tb where isnull(name1,'') <>''



--向表中插入每一级的子节点
insert into tb_tree
select id2,id1,name2
from tb where isnull(name2,'') <> ''

--向表中插入每一级的子节点
insert into tb_tree
select id3,id2,name3
from tb where isnull(name3,'') <> ''

select * from tb_tree

drop table tb_tree,tb