Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    56

    Unanswered: Creating Copy of Oracle Table on Local Sql Server

    I am looking to create a copy of an Oracle table locally on my Sql Server Box. I reference this table, and it can be very slow transferring over the WAN when I am getting a large dataset. So I want to create a local copy so reports will run quicker.

    Right now I am planning on just keeping track of all transactions in the past year, and only using about 12 of the 60+ columns on this table. Since this table is a billing table, sometimes transactions in the past change. So something purchased on 10/01 could be returned and that row is changed.

    My plan was to create a copy of the data I need for the past year in a seperate table on the Oracle server, and then copy this to my SQL Server. Then everyday, check what differences there are in my copied table and the main table using the MINUS function, and transferring those changed over.

    So the initial copy will take about 30 hours total. The everyday difference check takes about 1hr on the server, and 20 minutes to transfer. This way if need to look at a large data set, like all transactions in the past Quarter, it won't take me 8 hours just to get the data on my Sql server machine.

    I am just looking for comments on my methodology, if you guys think this is a good approach, or is there a better way to do this? Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    how did you transfer the data?

    What was the size of the file?

    30 hours?

    we talking terabytes

    How many rows?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2009
    Posts
    56
    Quote Originally Posted by Brett Kaiser View Post
    how did you transfer the data?

    What was the size of the file?

    30 hours?

    we talking terabytes

    How many rows?
    10 GB was the table size on the server.

    About 100 million rows.

  4. #4
    Join Date
    May 2009
    Posts
    56
    I transferred the data by using SSIS dataflow.

Posting Permissions

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