How MERGE few databases to big one

Hi All,
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
Regards
Actually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>
|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp
|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
..ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>
|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>

How MERGE few databases to big one

Hi All,
I have to merge few different databases to one big common database :)
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
--
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables in
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign keys
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hi All,
> I have to merge few different databases to one big common database :)
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
--
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring over
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups>)... that is, physically move the files
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:
> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>sql

How MERGE few databases to big one

Hi All,
I have to merge few different databases to one big common database
I mean about structure (tables, procedures, users, roles etc.) and
data. May you suggest, help me how I should do this. How start? Create
brand new database or relay on one old and add difference from other?
RegardsActually merging them is an option. The architecture should depend on other
factors such as how to maintain speed and integrity. If one of the tables i
n
another database is actually a subset of a table in the first database - for
example customers and customer address detail, you may get better results
moving it because you can use the database to create primary and foreign key
s
and thus maintain the integrity with constraints. If on the other hand, you
don't need this, you can simply join the tables from each database to make
your query:
Example Select * from database1.dbo.table1 inner join database2.dbo.table2
on table1.id=table2.id. And finally, you should consider your server
hardware. Can you get better results if you spread your non-clustered
indexes over one raid, clustered on a second raid and log files on a third,
or do you have just two raids or only one - or will a clustered system work
because you have lots of hardware?
The question you ask should be more specific.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hi All,
> I have to merge few different databases to one big common database
> I mean about structure (tables, procedures, users, roles etc.) and
> data. May you suggest, help me how I should do this. How start? Create
> brand new database or relay on one old and add difference from other?
> --
> Regards
>|||Hello,
Thanks, I mean how marge database on the physical level (as dba) not
developer. I'd like move all object from old databases to new (tables,
procedures, users, roles...) and then sql developers can change
schema (key, constraints, references etc.)
Do you know which tool can help me with this process. I'm sure i will
have problem with procedures and hardcoded old database names like
select * from old_db
I have to change
select * from new_db
and so on.
How automate this task, I have a lot of procedures, views and manually
changing will be difficult.
Regards,
anxcomp|||It will depend on what kind of databases you are importing but in general,
there is an import wizard built into SQL Server that allows you to bring ove
r
tables and views... if the databases are not sql (.mdf extension).
Otherwise, if you have SQL, you should backup your database, detach it,
physically move the files (.mdf and .ldf and be careful, you may have some
.ndf associated as well - <filegroups> )... that is, physically move the fil
es
to the new server and reattach them. Again, lots of right clicking here...
right click "database" in the solution explorer on the server and select
tasks... there are a bunch of them.
But I suspect you are moving database from sources other than sql.
Probably the best way to do this is to create an empty database on your
source server, right click the database (as above) when you make it and try
the import wizard out. If is fairly self-explanatory. It won't move
everything but will automate much of what you are trying to do.
--
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>|||To be sure, here are some links
attach and detach http://msdn2.microsoft.com/en-us/library/ms189625.aspx
import wizard http://msdn2.microsoft.com/en-us/library/ms140052.aspx
import from excel http://support.microsoft.com/kb/321686
Regards,
Jamie
"anxcomp@.gmail.com" wrote:

> Hello,
> Thanks, I mean how marge database on the physical level (as dba) not
> developer. I'd like move all object from old databases to new (tables,
> procedures, users, roles...) and then sql developers can change
> schema (key, constraints, references etc.)
> Do you know which tool can help me with this process. I'm sure i will
> have problem with procedures and hardcoded old database names like
> select * from old_db
> I have to change
> select * from new_db
> and so on.
> How automate this task, I have a lot of procedures, views and manually
> changing will be difficult.
> --
> Regards,
> anxcomp
>

How is inheritence like data best done in SQL

If you have several entities that have many common properties but a few
have a few unique fields to them how do you design your tables?

DO you make a seperate table for each entity even though they have many
common fields or is there a way to do an OO type thing where you have a
common table for all and somehow tack on the unique fields?

Just unsure whats possible and what's best.

Thanks for any input.On 18 Oct 2005 08:03:59 -0700, wackyphill@.yahoo.com wrote:

>If you have several entities that have many common properties but a few
>have a few unique fields to them how do you design your tables?
>DO you make a seperate table for each entity even though they have many
>common fields or is there a way to do an OO type thing where you have a
>common table for all and somehow tack on the unique fields?
>Just unsure whats possible and what's best.
>Thanks for any input.

The standard way I've always seen and often do is to have a "base" table with
the common fields, and a 1-to-1 relationship to tables with fields for the
specific case. There's even a symbol for this used on database diagrams.

Here's an example

address
address_id
country
country_subdivision
city
postal_code

street_address
address_id
street_name
street_number

postal_address
address_id
postal_box

Every address has an "address", and every address will have either a
"street_address" or a "postal_address", but not both.|||Ok, so is the idea is to remember to always do outer joins w/ the
address table to get all the info available?|||On 18 Oct 2005 08:36:39 -0700, wackyphill@.yahoo.com wrote:

>Ok, so is the idea is to remember to always do outer joins w/ the
>address table to get all the info available?

Once you have the structure, there are lots of options for how to retrive data
from it. An outer join to each and every "child" table is one option, or you
can add an address type column, and have the client do a second query to
retrieve the details of an address from the appropriate place.|||(wackyphill@.yahoo.com) writes:
> If you have several entities that have many common properties but a few
> have a few unique fields to them how do you design your tables?
> DO you make a seperate table for each entity even though they have many
> common fields or is there a way to do an OO type thing where you have a
> common table for all and somehow tack on the unique fields?
> Just unsure whats possible and what's best.

Basically as Steve says.

One has to be a little careful, and not overdo it. If it's only one or
two extra columns, maybe it's better to keep them in the main table.
Or let several "subclasses" share a table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 18 Oct 2005 22:06:57 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

> (wackyphill@.yahoo.com) writes:
>> If you have several entities that have many common properties but a few
>> have a few unique fields to them how do you design your tables?
>>
>> DO you make a seperate table for each entity even though they have many
>> common fields or is there a way to do an OO type thing where you have a
>> common table for all and somehow tack on the unique fields?
>>
>> Just unsure whats possible and what's best.
>Basically as Steve says.
>One has to be a little careful, and not overdo it. If it's only one or
>two extra columns, maybe it's better to keep them in the main table.
>Or let several "subclasses" share a table.

I concure with that. My example, in fact, is a case where having 3 tables
instead of optional fields is usually overkill.