0

I have data in a table in Oracle on one server that I want to copy daily to a table in SQL Server 2008 on another server.

My solution (which works nicely, but is a somewhat cumbersome solution) is as follows:

I run the following batch file on the Oracle machine

dumpdata --schema=mydb/mypassword --source-query="select * from MyTable" 
MyTable >> q:\output1.sql

dumpData comes from the cx_OracleTools package, and q: is a networked drive on the SQL Server machine

I then run the following batch file on the SQL Server machine

sqlcmd -i before.sql
ssr.exe 0 "to_date(" "convert(datetime," output1.sql
ssr.exe 0 "'YYYY-MM-DD HH24:MI:SS'" "101" output1.sql
ssr.exe 0 ");" "); /SSR_NL/ GO /SSR_NL/" output1.sql
sqlcmd -i output1.sql -o error.txt

The first line runs a SQL that deletes the rows from the table in the SQL Server database. The second, third and fourth lines converts the dates into a format that SQL Server understands. The fifth line imports the lines into the SQL Server database.

Now, the dumpData routine (because it creates INSERT statements) creates huge files, so I changed the dumpData to do the following:

dumpdata --schema=mydb/mypassword --source-query="select * from MyTable 
where to_char(update_date,'yyyy')='2012'" MyTable >> q:\output1.sql

Now I have an update_date field in the table, which changes whenever the row is updated. So I thought I would copy only those rows which have changed in 2012 (which is a manageable size) and import those to the SQL Server database.

However it is not enough for me to delete those rows in the SQL Server database which changed in 2012 in the SQL Server database and then do the import. I need to delete all those rows in the SQL Server Database which were changed in 2012 in the Oracle database.

If I could somehow convert my text file of INSERTS to an UPSERT (See Solutions for INSERT OR UPDATE on SQL Server) then my problem would be solved.

Alternatively, I could use SSIS 2008.

Does anyone have any simple ideas where I could tweak my existing solution to work?

Thank you...

Community
  • 1
  • 1
gordon613
  • 2,522
  • 10
  • 48
  • 77
  • 1
    "Alternatively, I could use SSIS 2008." - This is the way to go – msmucker0527 Apr 03 '12 at 17:28
  • Thank you msmucker0527. Since I am not looking for a long-term solution, and the Oracle Server is somewhat shaky, I would have preferred to try and tweak my existing solution, rather than installing new things on/rebooting the Oracle Server. – gordon613 Apr 03 '12 at 17:47
  • I don't believe you need to install anything on the Oracle Server in order to use SSIS, you would likely need to install something where the SSIS package is being run from – msmucker0527 Apr 03 '12 at 18:05
  • 1
    If SSIS was not an option then why did you list it as on option in your answer. This is what SISS was designed to do. Why not Bulk Insert for step 5? – paparazzo Apr 03 '12 at 23:05
  • Thank you msmucker0527. I have been looking into SSIS 2008 as you suggest, and indeed one needs to install the Oracle Client just on the SQL Server machine, as you say. However, all-in-all the reason I even asked my question, is because I thought there might be a simple tweak, rather than investing a lot of time in getting SSIS working. (And in reply to Blam, I mentioned it, just to show that I was aware of this possibility.) – gordon613 Apr 04 '12 at 17:59

0 Answers0