Several syntax errors / MSSQL to MySQL

browski's profile image browski posted 10 months ago in Import/Export Permalink

I'm new to this forum, so I hope it's not a dump question.

I use HeidiSQL to export data from a rather old MSSQL DB. The connection works fine, and I can use die SQL Data Export Feature as supposed. E.g. exporting a single table in a single sql-file (table creation, single inserts). However, the resulting sql syntax is kind of odd. It contains several entries that seem not to be valid SQL.

Here is the beginning of an example output:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES  */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE TABLE IF NOT EXISTS "KuenstlerKuenstler" (
    "KkuId" INT NOT NULL,
    "KkuMutationD" DATETIME NULL DEFAULT NULL,
    "KkuMitarbeiterS" NVARCHAR(50) NULL DEFAULT NULL COLLATE 'Latin1_General_CI_AS',
    "KkuKueId" INT NULL DEFAULT NULL,
    "KkuRefKueId" INT NULL DEFAULT NULL,
    "KkuAufId" INT NULL DEFAULT NULL,
    "KkuInfo01S" NVARCHAR(250) NULL DEFAULT NULL COLLATE 'Latin1_General_CI_AS',
    "KkuInfo02S" NVARCHAR(250) NULL DEFAULT NULL COLLATE 'Latin1_General_CI_AS',
    "KkuBeziehungS" NVARCHAR(252) NULL DEFAULT NULL COLLATE 'Latin1_General_CI_AS',
    "KkuBemerkungS" NVARCHAR(252) NULL DEFAULT NULL COLLATE 'Latin1_General_CI_AS',
    "KkuNachweisS" NVARCHAR(252) NULL DEFAULT NULL COLLATE 'Latin1_General_CI_AS',
    "KkuKue1Id" INT NULL DEFAULT NULL,
    "KkuKue2Id" INT NULL DEFAULT '0',
    FOREIGN KEY INDEX "FK_KueId_KkuKueId" ("KkuKueId"),
    PRIMARY KEY ("KkuId"),
    CONSTRAINT "FK_KueId_KkuKueId" FOREIGN KEY ("KkuKueId") REFERENCES "Kuenstler" ("KueId") ON UPDATE NO_ACTION ON DELETE CASCADE
);

/*!40000 ALTER TABLE "KuenstlerKuenstler" DISABLE KEYS */;
INSERT INTO "KuenstlerKuenstler" ("KkuId", "KkuMutationD", "KkuMitarbeiterS", "KkuKueId", "KkuRefKueId", "KkuAufId", "KkuInfo01S", "KkuInfo02S", "KkuBeziehungS", "KkuBemerkungS", "KkuNachweisS", "KkuKue1Id", "KkuKue2Id") VALUES
    (44, '2012-05-23 00:00:00.000', 'admin test', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 23028, 21272);

This causes several syntax errors when i try to import this to a mysql db.

First, the SET NAMES statements seems not to be valid SQL. Same with the ALTER TABLE part. The whole CREATE TABLE statement throws an error because the names of the db and the tables contain quotation marks. If I remove them, it works. Same with the INSERT statements.

I found no way to alter this output syntax. So what am I doing wrong?

Every hint on why the output is like that and on how I could fix this is appreciated.

ansgar's profile image ansgar posted 10 months ago Permalink

Exporting out of MS SQL with MySQL as a target system does not work in HeidiSQL. Parts of the SQL is then MS SQL specific (e.g. the " quotes around the identifiers, and the collation names), while other parts are MySQL specific (CREATE TABLE clauses).

I can just recommend not to use the SQL export for structure, while the data export could work fine.

ansgar's profile image ansgar posted 10 months ago Permalink

One more thing: "SET NAMES" is missing a character set name. MS SQL connections don't have a charset name. To make it compatible with MySQL, change it to "SET NAMES utf8mb4" (or "SET NAMES utf8" on older MySQL servers).

browski's profile image browski posted 10 months ago Permalink

Exporting out of MS SQL with MySQL as a target system does not work in HeidiSQL.

Ah, ok. Thanks for pointing this out! As the schema already exists on the target db, i will give csv a try. Many thanks for the fast reply.

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