I have 2 databases The databases are called db1 and db2 on sqlserver .db2
is an extension of db1 so it has many more tables and design changes. I need to migrate data from database db1 the earlier version , to the database db2. Can you suggest me how to design. please note that i just need design for documentation purpose to get it approved
The most important part of the work is to get the table and column mapping correct.
Beside getting the mapping correctly, you also need to consider the possibilty of key collisions. That is the key 7689 in db1.table1 may be a completely different entity than key 7689 in db2.table1. Hopefully you have natural keys (or at least natural-almost-keys)
that can help you find collisions on surrogate keys.
As for moving the data, that can be farily mechanical if volumes are modest. If the volumes are considerable, you may need to look into something smart. But since this is a one-off (I assume) it may not be worth to be too smart, so if it takes three days
to migrate, it takes three days to migrate.
Erland Sommarskog, SQL Server MVP, email@example.com
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.