I haven't done such thing before but you can try out something like this. Write some sql scripts which has some select insert statements (select form mysql insert into mssql). Hope this is not much complex since column names and table names are equal. You may need to write simple Java program to select queries and map them to insert statements which use both jdbc drivers. If you can use JPA this can be done easily.
Cheers, Dhanuka *Dhanuka Ranasinghe* Senior Software Engineer WSO2 Inc. ; http://wso2.com lean . enterprise . middleware phone : +94 715381915 On Mon, Apr 27, 2015 at 4:08 PM, Shani Ranasinghe <[email protected]> wrote: > > Hi, > > I have come across a situation where I need to migrate my MySQL db to > MSSQL in wso2 api manager. I have so far tried the following. > > 1) Using the ODBC connector and creating linked databases in MSSQL > In this approach, when importing the database, the schema seems to > change, for e.g. for tables that have Auto increment has been disappeared > when converting to a MSSQL schema, also the not null, null definitions have > been swapped at certain instances. > > 2) Used ODBC connector to import data to an existing database > I kept getting the error ": The Source - am_api_comments was unable to > retrieve column information for the SQL command. The following error > occurred: ERROR [42000] [MySQL][ODBC 5.3(a) > Driver][mysqld-5.1.73-community]You have an error in your SQL syntax; check > the manual that corresponds to your MySQL server version for the right > syntax to use near '"am_api_comments"' at line 1" for every table. > > 3) Tried some online tools > This too brought the errors in not converting the schema's properly as > step 1. > > 4) Exporting MySQL table by table as csv and importing them to MSSQL > This is the only way I could get it working. It is a very tedious way > of getting it done :( > i.e. 1) Export the data in MySQL data as csv file. When importing must > have a IFNULL check done for every varchar and not null column so that the > csv value is at the end correct so that the SQL can import it. An example > for this is that, when the MySQL column's have null values, when exporting > to csv, in the csv file it is needed to have a blank value, for this we > need to do the check. Once the csv's are ready from table to table, we can > import them to the MSSQL database, where we have already created the schema. > > > I am not sure if I did step 1 & 2 correct or if there was any issue in it. > I followed some online article and blog but could not succeed. If anyone > has any experience on this or knows of any other ways I could try please do > let me know. > > -- > Thanks and Regards > *,Shani Ranasinghe* > Senior Software Engineer > WSO2 Inc.; http://wso2.com > lean.enterprise.middleware > > mobile: +94 77 2273555 > linked in: lk.linkedin.com/pub/shani-ranasinghe/34/111/ab >
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
