#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2002
    Posts
    259
    Rep Power
    13

    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?
  2. #2
  3. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    792
    This can be broken down into two stages, depending on what you have set up.
    Stage 1:
    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

    Stage 2:
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    1
    Rep Power
    0

    Re: Transferring data between multiple sources


    Originally posted by bapolis
    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?
    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 mlemos@acm.org 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.
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539

IMN logo majestic logo threadwatch logo seochat tools logo