1

We used to have one database per client, and we're moving to using a single database. I need to find a way to merge the databases without losing data, including foreign keys. My primary keys are all integer.

What I understood is that the only way to do this is to update all primary keys and all foreign keys on all but one database (so that there's no conflict), then import them in a new db. Is this true?

If so, the only way I found is to manually set all my foreign keys to DEFERRABLE, then manually increment all my primary keys. Is this true? Is there a way to automate it to ALL my primary and foreign keys?

2

1 Answer 1

2

I have had this problem. Sometimes I have had to solve it the hard way. But others I simply take a large enough number and add it to ALL the numeric keys in one of the databases. Then I merge the two databases. Let's say if my biggest key in BD1 is 1213456, I add 1000000 to all the keys in BD2.

You can prepare your scripts querying information_schema to generate all modifications/alterations/updates. Something like (only PK)

SELECT CONCAT('UPDATE ', U.TABLE_SCHEMA,'.', U.TABLE_NAME, ' SET ', U.COLUMN_NAME, ' = ',U.COLUMN_NAME,' + 100000' )
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS    C
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U 
        ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
           AND U.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
           AND U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'

Will return queries like UPDATE dbo.Table1 SET id = id + 100000 And running results queries when you feel confident enough. Good luck!

Not the answer you're looking for? Browse other questions tagged or ask your own question.