Migrating data from SQL Server to mysql using inserts

Question

I am trying to migrate data from SQL Server to mysql, and i'd like to do that using insert-statements, so i modified this nice script by Narayana Vyas Kondreddi to generate mysql-friendly code and everything works fine so far.

the problem being columns of the TEXT datatype that can be more than 8000 characters long (containing long blocks of texts) and since the script uses a varchar(8000) to accumulate every script-row i fails when the data gets to long.

any nice suggestions on tools that allows me to do the similar thing, or any suggested solution? or maybe it's easier to write your own exporter with ado.net..

i tried the mysql migration toolkit and didn't get a successful result with that.

---

Edit: i ended up writing my own insert-generator using ado.net, it's available at: my mysql-framework svn if anyone is interested.

Answer 1

You can extend the varchar to 65535 bytes (MySQL 5.0 up), so an easy fix might be to modify the @Actual_Values variable definition in the script from Narayana Vyas Kondreddi...

--Variable declarations
DECLARE     @Column_ID int,         
        @Column_List varchar(8000), 
        @Column_Name varchar(128), 
        @Start_Insert varchar(786), 
        @Data_Type varchar(128), 
        @Actual_Values varchar(65535),  --This is the string that will be finally executed to generate INSERT statements
        @IDN varchar(128)       --Will contain the IDENTITY column's name in the table