层级数据如何插入

Question

--测试代码  
--DROP TABLE #tt
--drop table Dep;
--数据源
SELECT * INTO #tt FROM (
SELECT '部门4'  de union all
SELECT '部门1\部门10\部门100' de  union all
SELECT '部门4\部门40'  union all
SELECT '部门4\部门40\son'  union all
SELECT '天河区\综合部'   union all
SELECT '天河区\综合部\son'   union all
SELECT '海珠区\中心领导'    union all
SELECT '海珠区\综合部'  
) t;--部门表
CREATE TABLE Dep(Id INT PRIMARY KEY,Name NVARCHAR(200),ParentId int);
GOINSERT INTO [Dep] ([Id],[Name],[ParentId])
SELECT 1,'部门4',null;INSERT INTO [Dep] ([Id],[Name],[ParentId])
SELECT 2,'部门40',1; GO
 --说明:
--1. \是部门层级的分隔符.
--2.部分部门有可能是已存在的.
--3.部门的层级深度是未知的.
--4.根据部门表生成的 pathName肯定是唯一的.
--5.ParentId is null表示顶级父了.--求:
--请将数据源 #tt的数据 批量插入 Dep表中.
--(数据源大概可以拆分出上千个部门的. 游标写的还是算的.)--预期结果如下: 
--pathName语句
;WITH tx AS (
SELECT d.Id, d.Name, d.ParentId
,CAST(d.Name AS NVARCHAR(4000)) AS pathName
 FROM Dep d WHERE d.ParentId IS NULL
UNION ALL
SELECT d2.Id,d2.Name,d2.ParentId
,CAST(tx.Name+'\'+d2.Name AS NVARCHAR(4000)) AS pathName
 FROM Dep d2
INNER JOIN tx ON d2.ParentId=tx.Id
)SELECT * FROM tx ORDER BY tx.Id;
层级层级数据插入pathName

Answer 1

自问自答？
with可以递归解决

Answer 2

没自问自答啊!
递归查询还差不多
,可以递归插入?...递归查出结果,在插入
;WITH tx AS (
    SELECT d.Id, d.Name, d.ParentId
    ,CAST(d.Name AS NVARCHAR(4000)) AS pathName
     FROM Dep d WHERE d.ParentId IS NULL
    UNION ALL
    SELECT d2.Id,d2.Name,d2.ParentId
    ,CAST(tx.Name+'\'+d2.Name AS NVARCHAR(4000)) AS pathName
     FROM Dep d2
    INNER JOIN tx ON d2.ParentId=tx.Id
)SELECT * into #Tx1 FROM tx ORDER BY tx.Id;
select * from  #Tx1

Answer 3

根据数据源递归生成好结果集，可以进行插入
可就是对于 已存在部门数据,这个地方,比较难处理..我想如果重复的,就不要再插入了.. 
查询后 先关联一下结果表，不存在（关联失败）的才插入

我刚开始写的时候，也是这样想的.
到后来因为重复数据郁闷住了.
我已经给了示例代码了,麻烦帮忙实现一下!下面是split方法
  
--split方法
     
CREATE TABLE Nums(Rn int NOT NULL PRIMARY KEY);
GO ;WITH 
 t1 AS (SELECT 1 AS c UNION ALL SELECT 1 ) --SELECT * FROM t1;
,t2 AS (SELECT 1 AS c FROM t1 AS a,t1 AS b) --SELECT * FROM t2;
,t3 AS (SELECT 1 AS c FROM t2 AS a,t2 AS b) --SELECT * FROM t3;
,t4 AS (SELECT 1 AS c FROM t3 AS a,t3 AS b) --SELECT * FROM t4;
,t5 AS (SELECT 1 AS c FROM t4 AS a,t4 AS b) --SELECT * FROM t5;
,t6 AS (SELECT 1 AS c FROM t5 AS a,t5 AS b) --SELECT * FROM t6;
,tNums 
AS(SELECT ROW_NUMBER() OVER(ORDER BY t6.c) AS Rn FROM t6
)INSERT INTO Nums (Rn)  SELECT *  FROM tnums t WHERE t.rn<=10000000;GO
 
 
Create FUNCTION [dbo].[Split]  
(   
 @List nvarchar(MAX),  
 @SplitOn nvarchar(1)  
)  
RETURNS @RtnValue table (  
 Id int identity(1,1),  
 Value nvarchar(100)  
)  
AS  
BEGIN  
 INSERT INTO @RtnValue
 SELECT SUBSTRING(@List, n.Rn, CHARINDEX(@SplitOn, @List + @SplitOn, n.Rn) - n.Rn) AS [Value]
    FROM    dbo.Nums n
    WHERE   n.Rn <= LEN(@List)
           AND SUBSTRING(@SplitOn + @List, n.Rn, 1) =@SplitOn;
           
    Return  
END  
  
  GO
   

Answer 4

原来以为你的递归是解决了 a\b\c 的分解
原来只是 针对 id,pid 前者是难一点：declare 
@Dep TABLE(Id INT PRIMARY KEY,Name NVARCHAR(200),ParentId int);
 
INSERT INTO @Dep ([Id],[Name],[ParentId])
SELECT 1,'部门4',null;
 
INSERT INTO @Dep ([Id],[Name],[ParentId])
SELECT 2,'部门40',1;
 
 declare @tt table(de varchar(5000),fid int identity);
insert @tt
SELECT * --INTO @tt 
FROM (
    SELECT '部门4'  de union all
    SELECT '部门4\部门10'  union all
    SELECT '部门4\部门10\部门100'    de     union all
    SELECT '部门4\部门40'  union all
    SELECT '部门4\部门40\son'  union all
    SELECT '天河区'   union all
    SELECT '天河区\综合部'   union all
    SELECT '天河区\综合部\son'   union all
    SELECT '海珠区\中心领导'    union all
    SELECT '海珠区\综合部'   union all
    SELECT '海珠区'  
) t;--select * from @tt;
with d as (
select *,null fpid
from @tt where not de like '%\%'
union all
select a.*,b.fid
from @tt a
inner join d b on a.de like b.de+'\%'
and a.de not like b.de+'\%\%'
where --b.fid is not null
1=1 --right(a.de,len(a.de)-len(b.de)) not like '\%'
)
select  from d a
left join @dep b on a.de=b.name
where b.id is null

Answer 5

还是我自己弄吧。 
--测试代码  
--DROP TABLE #tt
--drop table Dep;
--数据源SELECT * INTO #tt FROM (
    SELECT '部门4'  de union all
    SELECT '部门1\部门10\部门100'    de     union all
    SELECT '部门4\部门40'  union all
    SELECT '部门4\部门40\son'  union all
    SELECT '天河区\综合部'   union all
    SELECT '天河区\综合部\son'   union all
    SELECT '海珠区\中心领导'    union all
    SELECT '海珠区\综合部'  
) t;--部门表
CREATE TABLE Dep(Id INT PRIMARY KEY,Name NVARCHAR(200),ParentId int);
GOINSERT INTO [Dep] ([Id],[Name],[ParentId])
SELECT 1,'部门4',null;INSERT INTO [Dep] ([Id],[Name],[ParentId])
SELECT 2,'部门40',1; GO
 --说明:
--1. \是部门层级的分隔符.
--2.部分部门有可能是已存在的.
--3.部门的层级深度是未知的.
--4.根据部门表生成的 pathName肯定是唯一的.
--5.ParentId is null表示顶级父了.--求:
--请将数据源 #tt的数据 批量插入 Dep表中.
--(数据源大概可以拆分出上千个部门的. 游标写的还是算的.)IF OBJECT_ID('tempdb..#newDepart') IS NOT NULL
BEGIN
DROP TABLE #newDepart;
END
IF OBJECT_ID('tempdb..#Depart') IS NOT NULL
BEGIN
DROP TABLE #Depart;
ENDIF OBJECT_ID('tempdb..#insertReslut') IS NOT NULL
BEGIN
DROP TABLE #insertReslut;
END 
 GO
 
--拆分
SELECT tx.de AS deSource
,r1.splitId,r1.depName,r1.newPathName,r1.newParentPathName
,ROW_NUMBER() OVER ( ORDER BY GETDATE()) rn  
 INTO #newDepart
 FROM #tt tx
OUTER APPLY(
SELECT sd.Id AS splitId,sd.[Value] AS depName
,lr1.newPathName
,lr2.newParentPathName
FROM dbo.[Split](tx.de,'\') sd
OUTER APPLY
(
SELECT STUFF((
SELECT '\'+ sda.[Value] from dbo.[Split] (tx.de,'\') sda WHERE sda.Id<=sd.Id
FOR XML PATH('')
),1,1,'') newPathName
) lr1
OUTER APPLY
(
SELECT STUFF((
SELECT '\'+ sda.[Value] from dbo.[Split] (tx.de,'\') sda WHERE sda.Id<sd.Id
FOR XML PATH('')
),1,1,'') newParentPathName
) lr2
) r1
 
;
-- 删除重复的;WITH tx AS (
SELECT nd.newPathName
,ROW_NUMBER() OVER (PARTITION BY nd.newParentPathName,nd.DepName ORDER BY nd.rn) rn 
FROM #newDepart nd
)DELETE tx WHERE tx.rn>1; 
--表的的pathName
;WITH tx AS (
SELECT d.Id, d.Name, d.ParentId
,CAST(d.Name AS NVARCHAR(4000)) AS pathName
 FROM Dep d WHERE d.ParentId IS NULL
UNION ALL
SELECT d2.Id,d2.Name,d2.ParentId
,CAST(tx.pathName+'\'+d2.Name AS NVARCHAR(4000)) AS pathName
 FROM Dep d2
INNER JOIN tx ON d2.ParentId=tx.Id
)SELECT * 
 INTO #Depart 
 FROM tx ORDER BY tx.Id;
 
 
 
--找出 将要插入数据的 parentId
 ;WITH tx AS (
SELECT nd.*,d.Id AS existDepId,d.Name AS existsDepName
,(
(SELECT isnull(max(d3.Id),0) FROM Dep d3)+ROW_NUMBER() OVER ( ORDER BY GETDATE())
) AS newDepId
,d2.Id AS newParentDepId
  FROM #newDepart nd
 Left join #Depart d ON nd.newPathName=d.pathName
 LEFT JOIN #Depart d2 ON nd.newParentPathName=d2.pathName
WHERE d.id IS null
)SELECT * INTO #insertReslut FROM tx;PRINT '实际插入begin'
--获取parentid插入数据
 INSERT INTO [Dep] ([Id],[Name],[ParentId])
 SELECT 
 ir1.newDepId,ir1.depName
 ,CASE WHEN ir1.newParentDepId IS NULL THEN ir2.newDepId ELSE 
  ir1.newParentDepId END AS newParentDepId
    FROM #insertReslut ir1
 LEFT JOIN #insertReslut ir2 ON ir1.newParentPathName=ir2.newPathName
 PRINT '实际插入end'
 
 
 
 GO
 

Answer 6

你建个已经弄好架构的数据表（用excel调整然后导入），然后以这个表来修改实际表不就行了。