Table Export to Database

everydayfam posted 2 years ago in Import/Export
I'm trying to export a table (with data) from an existing MS-SQL database to a new MySQL database. I'm selecting the Create checkbox when I perform the export.

The process fails with a syntax error. From what I can tell, this is occurring due to the double-quotes that are being placed around each object reference.

I can export to a SQL file and use find & replace in a text editor to remove all the " in the file and it loads properly into the MySQL database.

I was wondering if there was a better way to accomplish this, to save me those extra steps.

Thanks in advance.
jfalch posted 2 years ago
what exactly is an object reference ? can you give an sql example (with dblquotes still in) ?
everydayfam posted 2 years ago
Hello, my apologies about being vague (and probably using the wrong terminology). Here's what is getting exported :

CREATE TABLE IF NOT EXISTS "checklist_track" (
"checklist_track_id" INT NOT NULL,
"member_id" INT NOT NULL,
"group_id" INT NOT NULL,
"checklists_item_id" INT NOT NULL,
"modified_by" VARCHAR(100) NOT NULL);

That is kicking up a syntax error from MySQL. When I remove all the " it processes successfully.

Thanks for looking at this, let me know if you have any other questions.
everydayfam posted 2 years ago
My research seems to indicate that setting the SQL mode to ANSI_QUOTES would resolve this. But I'm not sure how to do this, or if it can be done within the Export to a DB process within HeidiSQL. See the below URL for more info:

ansgar posted 2 years ago
SQL exports in HeidiSQL work fine as long as you do not interchange data between MySQL and MSSQL. It's my goal to make this as compatible as it can be, but currently this is not done 100%. But who knows, probably setting ANSI mode also does it, although I guess you won't be able to import more complex data types, and indexes.

Fire this query on your MySQL server, just before you call the export dialog:

SET sql_mode:='ANSI'

everydayfam posted 2 years ago
Gotcha, I thought that this was the preferred way to convert from MSSQL to MySQL. Is there a better method I should be using?

If not, I'll use the code you provided. Thanks for the support and for the great product!

Please login to leave a reply, or register at first.