How to convert MS SQL queries to MySQL queries

Question

I have a MSSQL queries file(.sql), now I need to convert it to MYSQL queries. Please help me. The script like this:

CREATE TABLE [dbo].[Artist](
    [ArtistId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](120) NULL,
PRIMARY KEY CLUSTERED 

(
    [ArtistId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

That looks like DDL, not a Query? – StuartLC Nov 27 '12 at 12:06 — StuartLC, Nov 27 '12 at 12:06

score 3 · Accepted Answer · edited May 23 '17 at 12:26

If you want to convert the DDL by hand, then you can do this by building up rules on a case by case basis, e.g. as follows:

[] need to be replaced with backticks
IDENTITY(1,1) can be replaced with AUTO_INCREMENT
Most of the ANSI options and Device settings can be ignored (these seem to be present only because the table has been rescripted)
w.r.t. dbo, MySQL doesn't implement schemas in the same way as SQL Server - you will either need to separate schemas into databases, or drop the schema, or mangle the schema name into the tablename (e.g. as a Prefix)

This will leave you with something like the following:

CREATE TABLE `Artist`(
    `ArtistId` int NOT NULL AUTO_INCREMENT,
    `Name` nvarchar(120) NULL,
    PRIMARY KEY CLUSTERED 
    (
       `ArtistId` ASC
    )
);

Fiddle here

However, it is usually much easier to do this migration with a migration tool - search for the section on How to Transition from SQL Server to MySQL

How to convert MS SQL queries to MySQL queries

1 Answers1