Merge Two Databases Without A Long Nasty Insert / Update Script??

Question

Post reply

Merge Two Databases Without A Long Nasty Insert / Update Script??

anoel

Old Hand

Points: 388
More actions
May 18, 2007 at 2:27 pm

#178591

One of our customers who owns franchises has requested we combine to of their franchise databases into one. I've searched the web and found some old articles with people saying it couldn't be done without a long nasty insert / update script, which, like most SQL developers, I'd do ANYTHING to avoid. Is this still impossible with SQL 2005? We have over 350 tables in the database and it would take me a week just to write the script so if anyone has any suggestions, I'd be very greatful!!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jim Howell Ten Centuries Points: 1010 More actions · Answer 1

If the schema is the same on the two databases, a DTS package would work rather easily, if not, well...

anoel Old Hand Points: 388 More actions · Answer 2

The schemas are identical but my concern is keeping the relationships between tables in the old database when converted to the secondary database that already contains data. Is there a way to automate that using a DTS package without having to add columns to every table in my secondary database to store the old primary keys?

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

anoel,

I'm not sure what you mean. Do you mean that you have some primary key values in the old database tables that are identical to values in the secondary database tables, thus causing a duplicate key problem?

Greg

anoel Old Hand Points: 388 More actions · Answer 4

Hi Greg,

Sorry if I wasn't clear.....here's the scenario:

Company A has a database with customers, locations, invoices, etc.. Company B has purchased Company A and uses the same software (so same database schema, etc.). I've been asked to take all of Company A's data and add it to Company B's database. So if I have a table called tblCustomers with customerID being the primary key in Company A's database, I need to copy the data from tblcustomers to Company B's existing tblCustomers but I can't insert the primary key, obviously, since it already exists in Company B's database but for a different customer.

What I think I'm going to have to do is create a column in all tables in Company B's database called "OldKey" so when writing my INSERT statements I have a way to join back to Company A's database to import the records. Here's a quick script to lay out what needs to be done:

SELECT * FROM CompanyA.dbo.TblCustomers

INNER JOIN CompanyA.dbo.TblLocations

on tblCustomers.customerID = tblLocations.CustomerID

INNER JOIN CompanyA.dbo.TblJobs

on tblJobs.LocationID = TblLocations.LocationID

So that will give me all jobs associated with a location, which is associated with a customer from Company A's database. I need to keep those relationships intact when performing the import into Company B's existing database.

If it was just one table being imported, then it's a no brainer - a simple insert would be fine but in this case, there are hundreds of tables of normalized data so it's not that simple.

Thanks