CODEWITHSUNDEEP
mysqlsql-server
Phil
Phil

Reputation: 147

MySQL "World" database for MS SQL Server

Does anyone know where there might be a copy of the MySQL "World" example database online somewhere that is Microsoft SQL compatible? I don't have a running MySQL server on hand, just the SQL text file that SQL Server 2008 rejects.

Upvotes: 4

Views: 6303

Answers (1)

mellamokb
mellamokb

Reputation: 56779

It is now :-) http://pastebin.com/6ATaLuNs

I have been able to load the database into my own SQL Server installation by converting the data types from MySql to SQL Server version, removing MySql-specific code (such as engine specification), and using CHECK constraints in place of the enum's. Here's my rough attempt at converting the table structure script:

BEGIN TRANSACTION

CREATE TABLE city (
  ID int NOT NULL,
  Name char(35) NOT NULL DEFAULT '',
  CountryCode char(3) NOT NULL DEFAULT '',
  District char(20) NOT NULL DEFAULT '',
  Population int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
)

CREATE TABLE country (
  Code char(3) NOT NULL DEFAULT '',
  Name char(52) NOT NULL DEFAULT '',
  Continent varchar(20) CHECK(Continent in ('Asia','Europe','North America','Africa','Oceania','Antarctica','South America')) NOT NULL DEFAULT 'Asia',
  Region char(26) NOT NULL DEFAULT '',
  SurfaceArea decimal(10,2) NOT NULL DEFAULT '0.00',
  IndepYear smallint DEFAULT NULL,
  Population int NOT NULL DEFAULT '0',
  LifeExpectancy decimal(3,1) DEFAULT NULL,
  GNP decimal(10,2) DEFAULT NULL,
  GNPOld decimal(10,2) DEFAULT NULL,
  LocalName char(45) NOT NULL DEFAULT '',
  GovernmentForm char(45) NOT NULL DEFAULT '',
  HeadOfState char(60) DEFAULT NULL,
  Capital int DEFAULT NULL,
  Code2 char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (Code)
)

CREATE TABLE countrylanguage (
  CountryCode char(3) NOT NULL DEFAULT '',
  Language char(30) NOT NULL DEFAULT '',
  IsOfficial char(1) CHECK(IsOfficial IN ('T','F')) NOT NULL DEFAULT 'F',
  Percentage decimal(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (CountryCode,Language)
)

COMMIT TRANSACTION

You can run the INSERT statements as-is if you make the following changes (you will have to manually pull out the INSERT statements by removing the CREATE TABLE code in the MySql script):

  1. Remove all instances of back-tick (`) (Find-Replace ` with nothing in an editor)
  2. Replace all instances of \' with '' for SQL Server's quote escaping

Upvotes: 5

Related Questions