Over the last few years I've developed several databases for different departments. Now I'm in the process of re-developing those 4 into a single database for all of the departments. The issue is that the different databases all have an identity column for the Primary Key. The new database separates some of the data in those tables into multiple tables and combines the demographic information from those additional tables in 1 table. Any ideas on how I can accomplish this?
couple of options come to my mind, depends how much foreign keys dependencies you have and how much data you have.
- While keeping the old databases intact do a migration into new database and new table structures. keep all old identity columns in all tables around in new tables you are migrating to for sanity checks. then update all foreign key references to "new" identity column. then kill off "old" identity columns or keep them for posterity. Do you have a dev environment you can copy prod data to? you can do this exercise a couple of times and vet it out in dev environment and then push to staging/qa and then to prod? is there a front end associated with this database? you can stand up the front end/mid tier etc in preprod and test.
Yes I'm in full control of SQL Server and I'm also the designer of the front end. Thanks I'll start working on that immediately.
actually that is not good design. instead create a new FK column with no sorts of actual FK referential integrity. this new column is what I would update with new FK data from new table's primary key.
Thanks. I had planned on doing that. Set up a field that will take the first four of the last name combined with the first four of the firstname and the DOB in the format mmddyyyy and use that for the FK Column.