Converting MySQL DDL to SQL Server DDL

Question

I have a model that was generated for MySQL 5 but now I need to create these tables on a SQL Server installation.

It's been years since I mucked with SQL server and I want to make sure I can convert this script to be compatible.

I don't really know what to look for TBQH, so without further ado, here's my MySQL DDL

CREATE SCHEMA IF NOT EXISTS `bof_survey` DEFAULT CHARACTER SET utf8 COLLATE default collation ;
USE `bof_survey`;

-- -----------------------------------------------------
-- Table `bof_survey`.`question`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bof_survey`.`question` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `text` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bof_survey`.`category`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bof_survey`.`category` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `adverb` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `bof_survey`.`answer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bof_survey`.`answer` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `question_id` INT UNSIGNED NULL ,
  `category_id` INT UNSIGNED NULL ,
  `text` VARCHAR(60) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_answer_question` (`question_id` ASC) ,
  INDEX `fk_answer_category1` (`category_id` ASC) ,
  CONSTRAINT `fk_answer_question`
    FOREIGN KEY (`question_id` )
    REFERENCES `bof_survey`.`question` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_answer_category1`
    FOREIGN KEY (`category_id` )
    REFERENCES `bof_survey`.`category` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Answer 1

One way of getting started is to load your DDL into a MySQL database and then use mysqldump --compatible=mssql to re-dump it. That should get you started -- and from there on it may be going through the T-SQL docs and asking here on a case-by-case basis.

In addition, Microsoft has some resources, such as this article (for SQL Server 2000, but it could help mapping the data types correctly).

Answer 2

Peter,

there are some differences between MySQL and MSSQL that you need to consider here, especially schemas. I am not too sure how they work with MySQL, but it seems to be almost like what MSSQL calls a database in its own right.

A schema in MSSQL is more a security abstraction layer and used to group objects inside a database. It is something that is not greatly used AFAIK, but something that MS would like to promote. I have left it out here, and the objects are then created in the default schema (normally dbo).

Needless to say, the rest is quite straight forward:

-- ----------------------------------------------------- 
-- Table question
-- ----------------------------------------------------- 
IF NOT EXISTS ( SELECT  *
                FROM    sys.objects
                WHERE   name = 'question' ) 
    BEGIN
        CREATE  TABLE question
            (id int IDENTITY(1, 1)
                    NOT NULL,
             text varchar(255) NOT NULL,
             PRIMARY KEY (id)) ; 
    END 

-- ----------------------------------------------------- 
-- Table category 
-- ----------------------------------------------------- 
IF NOT EXISTS ( SELECT  *
                FROM    sys.objects
                WHERE   name = 'category' ) 
    CREATE  TABLE category
        (id int IDENTITY(1, 1)
                NOT NULL,
         name varchar(45) NOT NULL,
         adverb varchar(45) NOT NULL,
         PRIMARY KEY (Id)) ; 

-- ----------------------------------------------------- 
-- Table answer 
-- ----------------------------------------------------- 
IF NOT EXISTS ( SELECT  *
                FROM    sys.objects
                WHERE   name = 'answer' ) 
    CREATE  TABLE answer
        (id int IDENTITY(1, 1)
                NOT NULL,
         question_id int NULL,
         category_id int NULL,
         text varchar(60) NULL PRIMARY KEY (Id),
         CONSTRAINT fk_answer_question FOREIGN KEY (question_id) REFERENCES question (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
         CONSTRAINT fk_answer_category1 FOREIGN KEY (category_id) REFERENCES category (id) ON DELETE NO ACTION ON UPDATE NO ACTION);

CREATE INDEX fk_answer_question ON answer(question_id ASC) 
CREATE INDEX fk_answer_category1 ON answer(category_id ASC) 

Please note the following changes:

• AUTO_INCREMENT is swapped for IDENTITY. You specify the start value and the increment
• MSSQL doesnt have the notion of SIGNED or UNSIGNED ints
• The Primary key will be created as a clustered index by default
• The indexes will be created as non-unique and non clustered unless specified

The columnname 'text' is a reserved keyword and should be changed too, to stop any parsing problems.

Hope that helps.

Answer 3

Another possibility (if you have access to the MySQL database itself, as opposed to the DDL) is to use the migration wizard that Microsoft has released:

For migrating to SQL Server 2008: http://www.microsoft.com/downloads/details.aspx?FamilyID=0e6168b0-2d0c-4076-96c2-60bd25294a8e&displaylang=en

For migrating to SQL Server 2005: http://www.microsoft.com/downloads/details.aspx?FamilyID=c6f14640-da22-4604-aaaa-a45de4a0cd4a&displaylang=en

I've used the wizard for SQL Server 2008...it works well.

-Brian