Merge Two Databases Without A Long Nasty Insert / Update Script??
-
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!!!!
-
If the schema is the same on the two databases, a DTS package would work rather easily, if not, well...
-
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?
-
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
Greg
-
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply