Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003

    Unanswered: Automate Oracle import to SQL

    We are looking at setting up a SQL report server which automates the task of importing Oracle tables. However it seems to be an unknown how to reliablily and automatically import tables from Oracle into SQL. The total size is 500Meg now but will probably grow to a couple of gigs before long. Importing needs to not take too long and would be good if people could still query the data while the update is occuring. So I believe we need to append tables only.

    Any suggestions for this noob.


  2. #2
    Join Date
    Aug 2003
    Andover, MA
    Well, the basic approach would be to create a set of stored procedures which would append the SQL tables from the Oracle tables based on simple outer joins. They could be kicked off periodically as a SQL Server Agent job.

    DTS (Data Transformation Services) allows you do do the same thing, but gives you a nice GUI for configuring everything. You add a server connection for the Oracle server, a server connection for your SQL Server, and define individual transformation steps for each of your tables. You can customize the source SQL to include only new rows (again, outer joins based on table keys), and transform the data along the way if youd like. Again, the DTS package can be kicked off on a schedule by SQL Server Agent.

    I haven't done a lot with DB Replication, but I think it's pretty much SQL to SQL, so I'm not sure how much that would help. The first two approaches would give you everything you are looking for, I would think.
    I use the first approach to warehouse data from nine Informix servers every 5 minutes. Works pretty well.


  3. #3
    Join Date
    Jul 2003
    The Dark Planet
    Well ... the best approach would be to create a linked server and a repository of the tables you will need to transfer. Then you can write a stored procedure to dynamically make queries to transfer the data from oracle to SQL server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts