sql - 如何将记录从相互链接的表复制到另一个数据库中的另一个表？

我有3个相互关联的表。表的设计如下。

First(PK:FirstID，vchar:Name，int:Year)

秒(PK:SecondID，FK:FirstID，int:Day，int:Month)

第三(PK:ThirdID，FK:SecondID，int:Speed，vchar:Remark)

我正在尝试将记录从数据库A的3个相互链接的表复制到数据库B。所以我的Transact-SQL看起来像这样:

INSERT INTO First
(Name, Year)
SELECT  Name, Year
FROM    DB_A.dbo.First
WHERE Year >= 1992

INSERT INTO Second
(FirstID, Day, Month)
SELECT  FirstID, Day, Month
FROM    DB_A.dbo.Second S INNER JOIN
        DB_A.dbo.First F ON S.FirstID = F.FirstID
WHERE Month > 6

INSERT INTO Third
(SecondID, Speed, Remark)
SELECT  SecondID, Speed, Remark
FROM    DB_A.dbo.Third T INNER JOIN
        DB_A.dbo.Second S ON T.SecondID = S.SecondID INNER JOIN
        DB_A.dbo.First F ON F.FirstID = S.FirstID
WHERE Remark <> NULL

由于数据库B中的三个表为空，因此这些语句运行良好，直到数据库A和B中First.FirstID的起始位置不相同为止。因此，生成the constraint on foreign_key error。

可能的解决方案

重用旧的First.FirstID 我发现的解决方案之一是重用数据库A中的旧的First.FirstID。这可以通过在SET IDENTITY_INSERT TableName ON之前设置insert into TableName并将TableName.TableNameID包括在插入语句中来完成。但是，我建议同事不要这样做。

用新的First.FirstID覆盖Second.FirstID，然后用新的Second.SecondID覆盖Third.SecondID它们与表Second similar to this answer一起使用。但是，我仍然在如何将临时表中的Second.FirstID与正确的ID关联和替换。关于此操作的答案也将被接受为此问题的答案。

使用解决方案1，并使用OUTPUT更新主键和外键。我只是有这个主意，但我觉得这将非常乏味。需要做更多的研究，但是如果有答案说明如何成功实现此目的，那么我将接受该答案。

那么，如何将记录从3个相互链接的表复制到另外3个相似的表但主键不同？有没有比上述建议更好的解决方案？

最佳答案

您可以使用OUTPUT Clause。

CREATE TABLE #First (NewId INT PRIMARY KEY, OldId INT)

INSERT INTO First
(
    Name,
    Year,
    OldId -- Added new column
)
OUTPUT Inserted.FirstID, Inserted.OldId INTO #First
SELECT
    Name,
    Year,
    FirstID -- Old Id to OldId Column
FROM
    DB_A.dbo.First
WHERE
    Year >= 1992

第二表

CREATE TABLE #Second (NewId INT PRIMARY KEY, OldId INT)
INSERT INTO Second
(
    FirstID,
    Day,
    Month,
    OldId -- Added new column
)
OUTPUT Inserted.SecondID, Inserted.OldId INTO #Second
SELECT
    OF.NewId, --FirstID
    Day,
    Month,
    SecondID
FROM
    DB_A.dbo.Second S INNER JOIN
    DB_A.dbo.First F ON S.FirstID = F.FirstID INNER JOIN
    #First OF ON F.FirstId = OF.OldId -- Old ids here
WHERE
    Month > 6

最后一个

INSERT INTO Third
(
    SecondID,
    Speed,
    Remark
)
SELECT
    OS.NewId, -- SecondID
    Speed,
    Remark
FROM
    DB_A.dbo.Third T INNER JOIN
    DB_A.dbo.Second S ON T.SecondID = S.SecondID INNER JOIN
    DB_A.dbo.First F ON F.FirstID = S.FirstID INNER JOIN
    #Second  OS ON S.SecondID = OS.OldId
WHERE Remark <> NULL