migrating mysql to sql server 2008

Question

i have a simple mysql database. it has 3 tables.

here is the create statement for them:

CREATE DATABASE `qcvalues` /*!40100 DEFAULT CHARACTER SET latin1 */

CREATE TABLE `batchinfo` (
  `rowid` int(11) NOT NULL AUTO_INCREMENT,
  `datapath` mediumtext,
  `analysistime` varchar(50) DEFAULT NULL,
  `reporttime` varchar(50) DEFAULT NULL,
  `lastcalib` varchar(50) DEFAULT NULL,
  `analystname` varchar(150) DEFAULT NULL,
  `reportname` varchar(150) DEFAULT NULL,
  `batchstate` varchar(150) DEFAULT NULL,
  `instrument` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`rowid`),
  UNIQUE KEY `rowid_UNIQUE` (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=15771 DEFAULT CHARSET=latin1


CREATE TABLE `calibration` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(100) DEFAULT NULL,
  `qcname` varchar(50) DEFAULT NULL,
  `compound` varchar(150) DEFAULT NULL,
  `response` varchar(50) DEFAULT NULL,
  `isid` varchar(150) DEFAULT NULL,
  `isidresp` varchar(150) DEFAULT NULL,
  `finalconc` varchar(50) DEFAULT NULL,
  `rowid` int(11) DEFAULT NULL,
  PRIMARY KEY (`rid`),
  UNIQUE KEY `rid_UNIQUE` (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=708947 DEFAULT CHARSET=latin1



CREATE TABLE  `qcvalues`.`qvalues` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `compound` varchar(50) DEFAULT NULL,
  `rt` varchar(50) DEFAULT NULL,
  `response` varchar(50) DEFAULT NULL,
  `finalConc` varchar(50) DEFAULT NULL,
  `qvalue` varchar(50) DEFAULT NULL,
  `rowid` int(11) DEFAULT NULL,
  PRIMARY KEY (`rid`),
  UNIQUE KEY `rid_UNIQUE` (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=463066 DEFAULT CHARSET=latin1;

i am writing to this database from excel:

Set cn = New ADODB.Connection
    cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
        "SERVER=kame1;" & _
        "DATABASE=qcvalues;" & _
        "USER=root;" & _
        "PASSWORD=password;" & _
        "Option=3"
    'lets get the batch info
    '
    ' open a recordset
    Set rs = New ADODB.Recordset

what would be the easiest way to migrate the entire mysql DB to sql server?

Answer 1

Maybe setting up an empty database in SQL Server, then in Management Studio use the Import Wizard to import the data and as a side effect, optionally generate the CREATE TABLE statements.

See the MS documentation on the Export/Import Wizard for details

Answer 2

Microsoft just recently announced a migration tool for supporting migrations from MySql to SQL Server:

Microsoft announces SQL Server Migration Assistant for MySQL

I haven't had a look at it, but it might be worth checking out; here is the download page of the tool (with a typical MS-style product name ;-):

Microsoft SQL Server Migration Assistant for MySQL v1.0

Answer 3

If you were looking for the SQL statements to match what you've posted:

CREATE DATABASE qcvalues 

CREATE TABLE batchinfo (
  rowid int IDENTITY(1,1) NOT NULL,
  datapath varchar(max),
  analysistime varchar(50) DEFAULT NULL,
  reporttime varchar(50) DEFAULT NULL,
  lastcalib varchar(50) DEFAULT NULL,
  analystname varchar(150) DEFAULT NULL,
  reportname varchar(150) DEFAULT NULL,
  batchstate varchar(150) DEFAULT NULL,
  instrument varchar(20) DEFAULT NULL ,

  CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED ([rowid] ASC)
)

• quoting ticks need to be removed
• primary key statement is different
• auto-incrementing value is different

Rinse + repeat for your other two tables, and you're good.

Then you'll need to get a new connection string from Excel suitable for SQL Server.