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;
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).
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.
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
