mmsql和sqlserver数据同步,在MS SQL Server和Oracle Server之间同步数据

My colleague and I are trying to find the best query to sync up an Oracle database with that of a SQL server. There are about 80k+ rows with ~19 columns of data in each row. We have a linked server setup between the two servers and we have a query that works but for 80k records, the query took 10 hours to copy the records over. I can post the query we used but I would like to have a fresh set of eyes. This is a new process so we aren't trying to retrofit a solution to existing code. LIke I said before, permissions aren't an issue, it is just a matter of getting the data from Point A to Point B in the quickest time. This is to be used on a coldfusion supported web site and the client would like to click a buttton to sync up the data but again, this is just "wish list" of requirements we are working with.

Additional Thoughs I'd like to add:

We have tried openquery and using linked server but both took about the same time to complete.

Most are varchar(64 bytes), a couple of varchar(128) and a couple of varchar(12 bytes).

One suggestion someone else made was to write the data to a flat file, ftp the flat file to Point B and then import it. That is a viable solution but the more steps we include, the more chances there are of something breaking.

Thanks in advance. I look forward to seeing y'alls solutions.

解决方案

I've had more success with an SSIS package than linked servers. If you use the Oracle DLL's, it's not too bad.