Merging rows from two databases

Question

I have two (or more) different databases, identically structured but each containing different data.

How would I go about merging the data of one database into the other? This is a harder problem than I first thought. I have a table with primary keys, so a simple INSERT INTO db1.table1 SELECT * FROM db2.table1 may fail with clashing primary keys.

How can I insert the new records into the end of the table, allowing the identity column to take over and giving me a new primary key, while also cascasding all the foreign keys references?

Is there a better way? I have been looking at bcp but that works on a per table basis and I'm going to lose all my references (ie, table2 won't know the primary key in table1 has changed so cannot update its foreign key id).

Answer 1

If the idea is that you want to append the records from TableA ind DB1 to TableA in DB2, then that should be fairly easy:

INSERT INTO db1.Table1
SELECT [Your Columns Here, but skip the PK]
FROM db2.Table1

However, it would probably be easier to use the Import Data wizard from SQL2k8 which uses SSIS to pull the data in bulk from one source (in this case: db2.table1) and put it in some destination (in this case: db1.table1).

Answer 2

You need to decide which table is the winner in case of (inevitable) conflicts. You can start with one table declared the winner by the business process, then add all records without key conflicts from the losing table, then decide how much of the data from the losing table you want copied to the winner. It may be certain fields, or it may only be records in related tables not in the primary table.

Answer 3

You can use a cursor that loops thought all rows in db2.table1, add each row into db1.table1, and update the FK in table2.

declare myCursor Cursor FOR 
SELECT @pk, col1, col2 FROM db2.table1

OPEN myCursor

declare @pk int, @col1 int, @col2 decimal

Fetch NEXT FROM myCursor INTO @pk, @col1, @col2 

While (@@FETCH_STATUS = 0)

BEGIN
    BEGIN TRANSACTION

    INSERT INTO db1.table1 (col1, col2) VALUES (@col1, @col2) -- Assuming your pk is identity column
    IF (@@IDENTITY > 0)
    BEGIN
        UPDATE db1.table2 SET fk = @@IDENTITY WHERE fk = @pk

        IF (@@ROWCOUNT > 0)
            PRINT 'Success ' + COnvert(varchar, @@IDENTITY)
        ELSE
            PRINT 'Failed ' + COnvert(varchar, @@IDENTITY)
    END

     COMMIT

    FETCH NEXT FROM myCursor INTO @pk, @col1, @col2 
END

CLOSE myCursor
DEALLOCATE myCursor