主页 / dba / 问题 / 329574
Accepted
Zoinky
Asked: 2023-07-24 05:37:15 +0800 CST

在具有相同表但不同身份的两个数据库之间复制数据

  • 772

我有表Listings， Media它是“源”数据库的子表Listings并基于ListingId“源”数据库进行链接，并且我在“目标”数据库中有相同的表。

将所有列表复制到目标表 将列表及其子项复制到目标Media表，请记住，目标Media表必须获取ListingId插入到目标Listings表的内容，这是一个identity column

INSERT INTO Listings ( Title, ...)
SELECT  Title..
FROM [source].[dbo].Listings
WHERE ListingId IN (
    SELECT s.ListingId
    FROM [source].[dbo].Listings s
    LEFT JOIN Listings d ON s.ImportedReferenceNumber = d.ImportedReferenceNumber
    WHERE d.ListingId IS NULL
);

-- Insert corresponding media records into destination.Media table with new ListingIds.
INSERT INTO Media ( ListingId, Url, ThumbnailUrl, TypeId)
SELECT New.ListingID, Url, ThumbnailUrl, TypeId
FROM [source].[dbo].Media m
INNER JOIN (
    SELECT s.ListingId, d.ListingId AS NEW_ListingId
    FROM [source].[dbo].Listings s
    LEFT JOIN Listings d ON s.ImportedReferenceNumber = d.ImportedReferenceNumber
    WHERE d.ListingId IS NULL
) AS NEW ON m.ListingId = NEW.ListingId;

第一部分工作正常，但没有任何内容插入到目标中，因为执行查询时，源表和目标表Media之间没有区别Listings

sql-server

1 个回答

  1. Best Answer

    这可以通过一个简单的循环和 SCOPE_IDENTITY() 来完成，但根据数据集的大小，可能会运行相当长的时间。

    另外，为了完成此任务，您需要在 Media.ListingId 上有一个非聚集索引。

     DECLARE @ListingId_Orig BIGINT,
        @ListingId_Max BIGINT,
        @ListingId_New BIGINT = 0
        

SELECT @ListingId_Orig = MIN(ListingId) FROM [source].[dbo].Listings WITH (NOLOCK)
SELECT @ListingId_Max = MAX(ListingId) FROM [source].[dbo].Listings WITH (NOLOCK)

WHILE @ListingId_Orig <= @ListingId_Max

BEGIN

INSERT INTO Listings ( Title, ...)
SELECT Title, ...
FROM [source].[dbo].Listings
WHERE ListingId IN (
    SELECT s.ListingId
    FROM [source].[dbo].Listings s
    LEFT JOIN Listings d ON s.ImportedReferenceNumber = d.ImportedReferenceNumber
    WHERE d.ListingId IS NULL
)
AND ListingId = @ListingId_Orig -- Ensure only one row is selected. 

SELECT @ListingId_New = SCOPE_IDENTITY()


-- Insert corresponding media records into destination.Media table with new ListingIds based on identity value of prior insert using SCOPE_IDENTITY(). 
INSERT INTO Media ( ListingId, Url, ThumbnailUrl, TypeId)
SELECT @ListingId_New, Url, ThumbnailUrl, TypeId
FROM [source].[dbo].Media m
INNER JOIN (
    SELECT s.ListingId, d.ListingId AS NEW_ListingId
    FROM [source].[dbo].Listings s
    LEFT JOIN Listings d ON s.ImportedReferenceNumber = d.ImportedReferenceNumber
    WHERE d.ListingId IS NULL
) AS NEW ON m.ListingId = NEW.ListingId
WHERE m.ListingId = @ListingId_Orig


IF @ListingID = @ListingId_Max
    BEGIN
        BREAK
    END
    
SELECT TOP 1 @ListingId = ListingId FROM [source].[dbo].Listings WITH (NOLOCK) WHERE ListingID > @ListingId

END

    其工作原理是，将源 Listings 表中的记录一一插入到目标 Listings 表中，使用 SCOPE_IDENTITY() 捕获 @ListingID_New 变量中新插入记录的 Identity 值，然后仅将来自原始媒体表的 ListingId 与刚刚插入到目标列表表中的 ListingId 相匹配，并使用 @ListingID_New 值设置目标媒体表的 ListingID。

    • 0

相关问题