copy data between 2 databases with same tables, but different identities

Question

I have tables Listings and Media which is a child table of Listings and linked based on ListingId in "source" database, and I have the same tables in "destination" dabase.

copy all listings to destination table Listings and its children to destination Media table keeping in mind that the destination Media table has to get the ListingId of what was inserted to the destination Listings table and that is a 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;

The first part works fine, but nothing gets inserted into destination Media because the query I have when executed, there is no difference between source and destination Listings table

Answer 1

This can be accomplished with a simple loop and SCOPE_IDENTITY(), but depending on the size of your data set, could run for quite some time.

Also, for any hope of this finishing, you need to have a nonclustered index on 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

This works by inserting records from the source Listings table into the target Listings table one by one, using SCOPE_IDENTITY() to capture the Identity value of the newly inserted record in the @ListingID_New variable, then inserting into the child Media table ONLY records from the original Media table that have a ListingId that matches the ListingId that was just inserted into the target Listings table, and setting the ListingID for the target Media table using the @ListingID_New value.