Hi, I'm writing a converter for my database from MSSQL Express Edition to Oracle. The amount of rows in the table is around 5 millions. On MSSQL side I use LINQ to SQL to select data. I'd use the same approach for Oracle, but unfortunately there's no LINQ to Oracle in .NET. I know there is open source LINQ to Oracle implementation, but I think I'll use simple System.Data.OracleClient namespace. My question is how to insert row to Oracle db at once? I'm afraid that if I'll call ExecuteQuery() per row it would take a huge amount of time. So is there any elegant solution for my problem? Thanks.
+3
A:
The easiest way would be to forgo the program and use SQL Server Integration Services. You can link directly from MSSql server to the Oracle database and push the data over.
Kevin
2009-08-13 12:37:58
2nd that, you can even store the package to run it again if it needs to be run on several databases
Vincent
2009-08-13 12:42:18
Nice approach, but unfortunately I have SQL Server Express edition
Sorantis
2009-08-13 12:49:45
For the purposes of this data transfer you could use an eval of full Sql Server or the development version which costs $50. The licensing issues might make things sketchy but it's something to consider.You could also take a look at other 3rd party apps which might generate insert scripts for you, for example, Red Gate tools. Your target is Oracle (which Red Gate doesn't support) but you should end up with an easily modifiable inserts' script for vanilla execution on the oracle db.
Paul Sasik
2009-08-13 13:16:39
A:
You could write an Oracle SP and call the same from your application. Is there any specific reason/ purpose that you require a separate converter application ? You could create a dblink from Oracle to SQL Server and connect using ODBC and then populate the data, as for time/date format mismatches you can always format them using to_char() function
Sathya
2009-08-13 12:39:42