I am doing a database migration work. I have to copy a database in MSSQL to MySql database. It was possible to come up with a small java utility to copy table stucture from MSSQL to MySql Database. Now i have to copy all data from MSSQL to MySql. I tried using resultset in java to obtain all data from a table but then it could only fetch a small part of data. Is there any alternate solution to get all data from table to resultset or to some other similar structure which i could possibly use, to insert the same data into mysql Db. There are more than 25,00,000 records for a table.
+1
A:
A JDBC result set should in principle allow you to iterate the entirity of a large query result.
However going via Java may not be the most efficient approach. Bulk export to a file and bulk import may be the way to go. It appears that MS has a bcp utility that may do the export.
djna
2009-09-16 07:14:25
s/may be/is/g .
Vinko Vrsalovic
2009-09-16 07:15:19
Thanx djna,Bulk export to a file from mssql?? Can u elaborate a bit.Thanks in advance again :)
Richie
2009-09-16 07:39:37
Some database vendors have utlities for exporting and importing data. It appears that MS do. I don't know how readily that export can be imported to MYSQL, but once you have a file to play with I'd expect any required transforms to be quite easy.
djna
2009-09-16 07:46:29
the tool in mssql is called bcp
Vinko Vrsalovic
2009-09-16 07:51:41
Thank you djna and Vinko
Richie
2009-09-16 12:41:56
A:
The best way to achieve a database migration like you describe is to use and ETL Tool - there's a good overview of ETL here:
http://en.wikipedia.org/wiki/Extract,_transform,_load
There's no reason why you wouldn't be able to do this with JDBC and so if you are set on rolling your own please elaborate on 'could only fetch a small part of data':
- what is the query you are running?
- are you getting an exception?
- which JDBC driver are you using to connect to MS-SQL?
Nick Holt
2009-09-16 07:25:27
hi Nick,Thank you for your response.I know that ideally resultset should pick up all data from database,but then its not working for me.* The query which I am trying to run is "SELECT * FROM <TableName>"* I am not getting any exception while executing the query* the jdbc driver is microsoft jdbc SQL Server Driver
Richie
2009-09-16 07:38:01
Hi Richie - the MS JDBC driver isn't that good (some might say intentionally). Try using the TDS driver - http://jtds.sourceforge.net/index.html
Nick Holt
2009-09-16 07:48:58