August 6th, 2002, 02:42 PM
Transferring data between multiple sources
I need to write a script that dumps data from one database (Oracle) and uploads it into a completely different database (MS SQL). Has anyone ever writen a script like this before? Are there any sites/tutorials that explain the fundamentals on how do to this?
August 6th, 2002, 04:12 PM
This can be broken down into two stages, depending on what you have set up.
If the receiving database does not have the table structure already defined in it, then you have to build the table structure. There can be problems here, it the table structure is valid for one database, but not on the other. This goes to include, data types, primary/foreign keys and other constraints. So it would be best to build just a basic table structure with known data types that will work for both database types.
PHP (if this is the language you are using) has the ability to get field names, data types and primary keys for almost all databases it supports, so building the table structure shouldn't be that difficult of a taskl
Dumping the data into a file in the format that can be uploaded into the receiving database. There are three options here.
1) A csv file (comma delimited) which most databases have support for uploading csv files.
2) Another defined delimited support, for which generally all databases support this also but can sometimes be more of a pain to upload. (i.e. a tab delimited file)
3) Export the contents of the database into sql insert statement according to the table structure.
I would say option 3 is the best route to go if you have a new table structure, or are relatively new to uploading data into a database. Each way will pose it's own risks. A well known tool for PHP uses on the mysql database is phpMySQLAdmin which has the options listed above and a few more, but is only for mysql.
August 8th, 2002, 06:08 PM
Re: Transferring data between multiple sources
This looks like a task for Metabase .
Metabase has now experimental schema reverse engineering support in such way that you can retrieve the schema of a database installed by some other application and then you can re-install it in any supported database, including MS SQL.
The support for this is not yet publically available in the address above but it is already in the beta version in the metabase-dev mailing list file archive. Just mail me at email@example.com if you are interested to use Metabase for this purpose and I can make sure it works for the particular databases that you are working with.
August 9th, 2002, 03:15 AM