Hello,

I have two mssql databases for a hotel booking engine, they have
identical structures but both contain completely different data. I
want to combine (or merge) the contents of both databases into one
database.

I have run into difficulties, however, due to issues with primary keys
and indexes. For example, I have a table called Rooms which contains,
as it's Primary Key, an autonumber int field called RoomID. There are
many other tables that use RoomID as a Foreign Key. Since I have used
an autonumber for this key, I cannot import it directly into the other
database without throwing many errors, due to the other database
having allocated the same numbers of different entries.

So, my question is, is there a simple way of importing structured data
from one database to another, preserving the structure of the
information while the database automatically reassigns the values of
the conflicting Primary Key fields? I don't want to delete the
conflicting data, I simply want to give it a new autonumber.

I have googled for days, consulted the online documentation, consulted
various books, but cannot come up with a solution for this. Has
anybody here had to do anything like this before?

Regards,
Armitage.

=========================
Email :ck (AT) in1nospamsolutions (DOT) com (remove nospam)

One way would be to

On the destination (The Consolidated database) hold a reference to the source system key value and the source system itself. This way you could update the foreign key links based on their new auto incremented values and keep the integrity intact.
It would also help to identify the source system for the record.




--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org