I'm wondering what is the best route to move image data in a SQL Server database to a MySQL server database? Is it possible via SQL?
So far I have created a linked server on the MS SQL Server that links to the MySQL table. I'm able to upload blobs on the MySQL server when I am on it locally (using the LOAD_FILE function...but trying to stay away from that since the images need to be on the MySQL host for that function to work). Is there another way to insert blobs into MySQL without using the LOAD_FILE?
I'm thinking there should be a way to break down the MS SQL Server image field to binary and then insert that into the MySQL database via the linked server?
Yes, I found that SQL Server seems to implicitly convert the image to varbinary(max)....I was able to get this to work using the OPENQUERY method.
{INSERT INTO OPENQUERY(MYSQLLINKEDSRV, 'select name, file from mysqlTable') SELECT TOP 10 name, file from mssqlTable; }
This seemed to work for me as well... {INSERT INTO MYSQL...mysqlTable(name, file) VALUES ('name','xxxxx')}
I did notice that when I was inserting into MySQL through the linkedserver that the first column of the inserted record was null....to fix this I moved my integer columns to the end of my insert statement and it seems to work fine, I'm still looking into how to handle this if there are no integer columns...don't really want to have a dummy column in my mysql table if I don't have to...