Combine two databases

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
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
|||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:
>
> --
> -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
|||Hi

> 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
>
You have not said if the combined database will have an extra identifiers to
say which database the data originated from. Also you if you have any
identity columns you will need to decide what you are to do with clashing
identities if they are used as foreign keys.
If there are no foreign keys and the structures are the same then you could
just do
exec sp_msforeachtable 'INSERT INTO ? SELECT * FROM [Otherdb].?'
If there are FKs or identity values you can use
exec sp_msforeachtable 'SELECT ''INSERT INTO ? SELECT * FROM [Otherdb].?'''
to get a script that you can then manipulate so it is in an order that will
satify the FK constraints and you can add SET IDENTITY_INSERT statements.
You may also want to look at http://vyaskn.tripod.com/code.htm#inserts
If you have a very large amount of data you may want to consider using BCP
instead.
John
|||> 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.
|||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.
sqlsql

Combine two databases

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 #613In 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|||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:
>
> --
> -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|||Hi

> 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
>
You have not said if the combined database will have an extra identifiers to
say which database the data originated from. Also you if you have any
identity columns you will need to decide what you are to do with clashing
identities if they are used as foreign keys.
If there are no foreign keys and the structures are the same then you could
just do
exec sp_msforeachtable 'INSERT INTO ? SELECT * FROM [Otherdb].?'
If there are FKs or identity values you can use
exec sp_msforeachtable 'SELECT ''INSERT INTO ? SELECT * FROM [Otherdb].?
'''
to get a script that you can then manipulate so it is in an order that will
satify the FK constraints and you can add SET IDENTITY_INSERT statements.
You may also want to look at http://vyaskn.tripod.com/code.htm#inserts
If you have a very large amount of data you may want to consider using BCP
instead.
John|||> 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.|||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.

Combine two databases

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 #613In 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:

Quote:

Originally Posted by

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|||Dave Markle wrote:

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

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|||Rodjk #613 wrote:

Quote:

Originally Posted by

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|||There are tools to compare the data in the two databases (Like Red

Quote:

Originally Posted by

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.|||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.

Combine two databases

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 #613In 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|||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|||> 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.|||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.

Collation Recommendation

I work for an international company and we have to join between databases in different locations. I want a situation where the collations are the same so that we don't have to worry about stating which collation to use in cross server queries. Can someone tell me what we shoud choose. We can't right now change the system to use unicode. If we choose windows collation then does it take the collation of the os on the server? Will we have a problem if different servers have different Windows collations? Is it better to choose a Sql collation and make sure everyone chooses the same one? Thanks for your help.What other DB platforms/collations will you be working with?
Do you have the ability/authority to dictate the collation used at these other sites?

I'm not sure what you mean by 'Windows collation'. When creating a new DB in SQL2k, you have the ability to set the code page/collation for that db and 'windows collation' is not an option.|||I can dictate what collation the other servers should use. When you setup the Sql Server you can choose either windows collation and the sort order or choose a Sql Collation and the sort order. What I want to know is whether or not it will simplify things to choose the Sql Collation. It seems to me that if I use the windows collation I may run into problems if the windows collation of the os is different on the other servers.|||Unless I have a reason not to, I use the default (SQL_Latin1_General_CP1_CI_AS). The 1252 character set, case insensitive, dictionary order, accent sensitive.

Being an international company, though , you may wish to use the 850 (multi-linqual) character set, but I'd stick with case insensitive, dictionary order, accent sensitive, unless you have another reason not to.

Unicode would be preferable, but you've already mentioned that you cannot use it.|||Hi!

I am looking for help concerning collation problem, so if You can help me I would be thankful...

I am changing from sql7 to sql2k and i would like to be sure i have maintaned the same sort order etc. i had in sql7:

Unicode data sorting
-------
Locale ID = 33280

case sensitive

Sort Order Description
----------------------------------------
Character Set = 4, cp1250
Code page 1250
Sort Order = 80, bin_cp1250
Binary Sort Order for the Code Page 1250 Character Set

Characters, in Order
----------------------------------------
! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@. A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~
_
_



So i think in sql2k i should set:

Polish_BIN

i need to have bin sorting and polish letters.

on collation list:

select * from ::fn_helpcollations()

there is not available:

sql_latin1_general_cp1250_bin

Please let me know if i am right or not.

Thanks in advance

by the way anybody knows how to get the list of ordered characters used by the sql server 2k same as the one extracted from the sql7. standard procedure sp_helpsort was used to receive the result at the start of this message, but for sql2k gives slightly different answer as:

only: Polish, binary sort

?