Combine databases

[junOOni]

Hello all,

I am relatively new to databases.

How can we combine different databases together? I have to combine SQL, Oracle and Sybase databases into one database. The target database is SQL.

All databases keep similar information, however, the schema/design of the database may differ from one another.

Now, the task at hand is to first create a SQL db schema that encompasses all the fields from other databases, then import the data from other databases.

Mind you, all three databases are quite big, over 50Gig size each.

Does anybody know how to handle this situation? What are the things i should keep in mind when starting this activity? and what is the best way to do this?

Thanks in advance,
J!

[rmiao]

Which version of sql server do you use? You can dump data from Oracle and Sybase tables to text files then load them into sql table with bcp, bulk insert or dts/ssis package. Column data type and data range may differ among those rdbms.

[junOOni]

Thanks for the prompt reply.

I am using Sql Server 2000.

Sorry to bug again. If the data types are going to differ, how can i make sure that the data is imported correctly? Can i convert the data types "on the fly" when using DTS to import data?

Also, when Import/Export using DTS, can we keep the constraints intact as well? e.g. the foreign keys stay as foreign keys in the new database? or will i have to create those relationships in the new database (SQL 2000)?

I would like to create a "composite key" when i am importing data from each database to avoid the conflict of data. e.g. invoice number being the same in two databases. I was thinking was to create an additional field to distinguish which database the data was extracted, and make that and the original ID as the key. Can i do that when importing? or how do i go about doing that?

Sorry if i am not vague in my questioning. I am really a beginner in this field.

Thanks again,
J!

[rmiao]

You need to create keys in sql table manually. It'll take a long while to transfer 50gb data between servers directly in dts, better to use separate data dump and data load processes.

[MAK]

use linked server and create views

[rmiao]

I think junOOni needs to consolidate Oracle and Sybase to sql server.

[junOOni]

Thanks rmiao and mak for replies.

Yes, i have to consolidate the databases. Actually, i have to create a separate db first in SQL 2k, then put all three dbs in the new db. The reason i have to do that is i have to only use 'common' items among the three dbs. Also, the standing sql2k db schema is a bit convoluted.

I had thought about using Linked server too, but honestly speaking, i didn't quite understand it. Also, i kinda figured it was used when you want to connect to other databases to use them in queries. (Will it be a good idea if i created a new database, then create linked servers to all three databases and use them in queries to populate the new database?, or am i just confusing myself?)

Also, depending on the design of a source database, i may have to play around with the values too as i am importing them, since some tables create a look up tables, others have used just a value for a field. e.g. one db has created a table for all the customers. In invoice table they just use there fk to know which customer the invoice belongs to. For the other db, it uses the name of the customer in the invoice table. Things like that.

Oh, how can i generate a "create table" query in oracle. I mean, I can generate these queries in SQL 2k, so if i want to run them on my new db i could do that and create all the keys with them. However, i couldn't get it to run in Oracle.

I am running the following query in ISQLplus:

SELECT dbms_metadata.get_data('TABLE', 'Inv') FROM dual;

I get the following error:

SELECT dbms_metadata.get_data('TABLE', 'Inv') FROM dual;
*
ERROR at line 1:
ORA-00904: "DBMS_METADATA"."GET_DATA": invalid identifier

I am new to SQL to begin with, here now, i have to deal with Oracle and Sybase as well.

Honestly, this is a bit overwhelming for me. But hey, i am liking it too. Challenge keeps me motivated!

Thanks for your advices!!
J!

[MAK]

This would be a good start.

Get a tool like ERWIN 7.1 or ERStudio. or download eval edition and reverse engineer Oracle and reverse engineer sybase and forward engineer as SQL Server 2005 script. You dont have a sweat a lot.

Linked server

http://www.databasejournal.com/featu...le.php/1756161