Migrating image(photo) data from SQL Server to MySQL

Care Consulting Ltd

Hi All,

Here's my Issue:

My Task

1. Unloading personnel Data from SQL Server with accompanying photo image for 1,000+ records
2. Insert the records into MySQL.

My Problem - Unable to Import image data from an unloaded Comma Delimited text file into MySQL.

What I Did - I use a SQL to unload the data to a file like so:

       SELECT ...,

                    convert(varbinary(max), table.PHOTO, 1) as i_photo

       FROM table

       WHERE ...

          

What happens - The rest of the data(Name, Age, etc.) loads into MySQL fine but the Image data does not!

Question - When I check the image field contents in the unload file I see a long string like "0xFFD8FFE100184578696...".

So I assume that to mean that the image data is in the file. What am I doing wrong with the image?

What is the right way to do a bulk load like this?

Any help is greatly appreciated.

Best Regards,

Patrick

Dave Stokes-MySQL Community Team-Oracle

You can store photos in a BLOB. I would recommend that you save the photo in a file on your server and then store the link to the photo in your table.