SQL Server To MY SQL

Question

I got a new project from my teacher to convert database to another. How can I convert a MS SQL database into MYSQL using JAVA

Answer 1

You will want to keep in mind that there are two logical steps regardless of the Programming environment. Firstly, you will want to map the schema of the database to an equivalent schema in the target database. This means mapping data types and constraints. Sometimes there are cases where it is simply not possible. Secondly, mapping the data from one database to the other. Timestamps and date formats must be equivalent for example. Hope that helps you to get started.

Answer 2

How can I convert a MS SQL database into MYSQL using JAVA

Dump it ( from MSSQL) and execute the result in Mysql.

With a question like that it's the best answer you can give

Answer 3

Look at the various metadata classes in JDBC:

java.sql.DatabaseMetaData
java.sql.ResultSetMetaData

These will allow you to get the structure from the MSSQL DB. You need to do a little experimentation as, if memory serves, what gets returned in the metadata can vary from database to database.

Once you've worked out how to get the metadata from the MSSQL DB you need to convert that into SQL commands to recreate the structure in the MySQL DB.

Then with the structure in place you can start copying the data between the two by creating insert queries that run against the MySQL DB from querying the contents of the MSSQL DB. You'll need to do this in the correct order so that the referential integrity isn't violated which again you'll be able to determine from the metadata.

It's an interesting academic exercise but in the real-world you'd use an Extract-Transform-Load (ETL) tool.

Answer 4

You could also try to use the hibernate tools to create a mapping to the MS SQL tables. Then use hibernate again with the created mapping to create the tables in MySQL.