SQL合并两个表并更新引用的ID

Question

我有两个表，我想将它们合并为一个表并使用 TypeID 来区分它们。假设类型是 A 和 B。表是 A_Level 和 B_Level

A 的表看起来像

Level
Level_ID 描述

B 的表看起来像

Level
Level_ID Level_Desc

A 的 Level_ID 从表 C 中引用为 Level_ID
B 的 Level_ID 从表 D 中引用为 Level_ID

我正在寻找一个脚本，它将两个表合并为一个表（Level_Code）并相应地更新引用的表 ID。

非常感谢任何帮助。

Answer 1

select a.Level_Id ALevelId, b.Level_Id BLevelId, 
       case ISNULL(a.Level_Id, 0) when 0 then 'B' else 'A' end AS Type,
       case ISNULL(a.Level_Id, 0) when 0 then b.Level_Id else a.Level_Id end AS NewLevel_Id
INTO Dummy       
FROM  a 
FULL JOIN  b On (a.Level_ID = b.Level_ID); 


UPDATE c
SET c.Level_id = Dummy.NewLevel_Id
from Dummy, c
WHERE c.Level_Id = Dummy.ALevelId 
AND Dummy.Type = 'A';

UPDATE d
SET d.Level_id = Dummy.NewLevel_Id
from Dummy, d
WHERE d.Level_Id = Dummy.BLevelId 
AND Dummy.Type = 'B';

SELECT Dummy.NewLevel_Id, a.Level, a.LevelDesc As Description
INTO YourNewTable
from Dummy JOIN a ON (Dummy.ALevelId = a.Level_Id)
Where Dummy.Type = 'A'
UNION
SELECT NewLevel_Id, Level, LevelDesc As Description
from Dummy JOIN b ON (Dummy.BLevelId = b.Level_Id)
Where Dummy.Type = 'B'

DROP TAble Dummy;