22,302
社区成员




;WITH T1
AS
(
SELECT *,[Path]=Name FROM 表1 WHERE parentID=0
UNION ALL
SELECT *,[Path]=b.[Path]+'-'+a.Name FROM 表1 AS a INNER JOIN T1 AS b ON b.ID=a.parentID
), T2
AS
(
SELECT *,[Path]=Name FROM 表2 WHERE parentID=0
UNION ALL
SELECT *,[Path]=b.[Path]+'-'+a.Name FROM 表2 AS a INNER JOIN T1 AS b ON b.ID=a.parentID
)
SELECT
a.*,b.ID AS ID2,b.parentID AS parentID2
INTO #
FROM T1 AS a
INNER JOIN T2 AS b ON b.[Path]=a.[Path]
UPDATE a
SET parentID2=ISNULL(b.ID2,0)
FROM # AS a
LEFT JOIN # AS b ON a.parentID=b.ID
UPDATE b SET topid=a.topid,parentID=a.parentID2 FROM # AS a INNER JOIN 表2 AS b ON b.ID=a.ID2
SELECT
a.*,b.ID AS ID2,b.parentID AS parentID2
INTO #
FROM 表1 AS a
INNER JOIN 表2 AS b ON b.name=a.name
UPDATE a
SET parentID2=ISNULL(b.ID2,0)
FROM # AS a
LEFT JOIN # AS b ON a.parentID=b.ID
UPDATE b SET topid=b.topid,parentID=b.parentID2 FROM # AS a INNER JOIN 表2 AS b ON a.name=b.name
CREATE TABLE #Tmp
( [ID] INT,
[NewID] INT
)
MERGE tb1
USING tb2
ON 1 = 2
WHEN NOT MATCHED THEN INSERT (XXX) VALUES (XXX)
OUTPUT tb2.ID,Inserted.ID INTO #Tmp(ID,NewID);
truncate table Table1;
go
insert into Table1
select * from Table2
或在SSMS里T1导入