]
07-31-2023, 09:56 AM
Mass database update to offset PKs: make a copy of the database instead.
Special use case: company A uses a database with the same schema as company B. Because they have merged, they want to use a single database. Hence, many tables from company B's database must have their primary keys offset to avoid collision with company A's records.
One solution could have been to define foreign keys as ON UPDATE CASCADE, and offset the primary keys having the foreign keys follow. But there are many hurdles if you do that (Msg 1785, Msg 8102, ...).
So a better idea that occurs to me is simply to make a copy of the database, DROP and re CREATE the tables that must have their PKs|FKs offset, and copy the data (and while doing so, offset the primary keys and the foreign keys).
Avoiding all the hassle.
Special use case: company A uses a database with the same schema as company B. Because they have merged, they want to use a single database. Hence, many tables from company B's database must have their primary keys offset to avoid collision with company A's records.
One solution could have been to define foreign keys as ON UPDATE CASCADE, and offset the primary keys having the foreign keys follow. But there are many hurdles if you do that (Msg 1785, Msg 8102, ...).
So a better idea that occurs to me is simply to make a copy of the database, DROP and re CREATE the tables that must have their PKs|FKs offset, and copy the data (and while doing so, offset the primary keys and the foreign keys).
Avoiding all the hassle.