By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 1,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Combine two databases

P: n/a
Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613

Jan 22 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.

There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.

-Dave

Rodjk #613 wrote:
Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613

--
-Dave Markle

http://www.markleconsulting.com/blog
Jan 22 '07 #2

P: n/a

Dave Markle wrote:
In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.

There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.

-Dave

Rodjk #613 wrote:
Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613


--
-Dave Markle

http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.

My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.

I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.

Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?

Thanks
Rodjk #613

Jan 22 '07 #3

P: n/a
Rodjk #613 wrote:
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Yes.

Imagine database A has order no 1, being shipped to customer no 1.
Database B also has an order no 1, being shipped to customer no 1.

But in database A customer no 1 is Mr. Black, in B it is Mr. White.

/jim
Jan 22 '07 #4

P: n/a
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.

Jan 22 '07 #5

P: n/a

Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.

Jan 22 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.