CODEWITHSUNDEEP
mysqlsql-serverdatabase-migration
Steffen Hvid
Steffen Hvid

Reputation: 187

MsSQL to MySQL Migration

I am trying to get all data from a table out of a MsSQL Database to import it into MySQL. Looking at guides it should be simple enough.

I choose Tasks -> Export Data Then i choose the..

..source as MsSQL Server Native client

..target as flat file (Locale=Danish and tick the Unicode checkbox)

..delimited by " (Double Quote)

..to remove the headers

..row delimiter as {CR}{LF} (Which is \r\n in MySQL?)

..column delimiter as , (comma)

And the run it immediately -> results in a file called test.csv

I go "Script table as" to get all the columns and their atributes, copy that into MySQL Workbench and adapt the code to the create format of MySQL.

LOAD DATA LOCAL INFILE 'D:\\test.txt' INTO TABLE test FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ;

And then i get the following error:

Error Code: 1300. Invalid utf8 character string: ''

This i don't really understand since i've exported into unicode. Any ideas?

Upvotes: 0

Views: 722

Answers (1)

Naveed Ramzan
Naveed Ramzan

Reputation: 3593

I am sure its formatting issue and best way is to do a script.

  • Connect database using ODBC and PHP (if you are using PHP), odbc_connect for example Conn1
  • Connect database using mysqli and PHP mysqli_connect for example Conn2
  • Get each record in array or object with connecting Conn1
  • Now insert record in Conn2

I hope it will help you. Thanks

Upvotes: 1

Related Questions