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.