Solved

Converting My SQL to MS SQL

Posted on 2014-03-30
6
297 Views
Last Modified: 2014-03-30
I need help to ensure that I had the syntax correct when converting MS SQL from My SQL when trying to create a table. For some reason I am having problems getting the syntax correct. See attach file.
SQL
0
Comment
Question by:WDJ98338
6 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39964608
Which one is correct and working?
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39964613
The mysql one works. Just tested it in phpmyadmin.
0
 

Author Comment

by:WDJ98338
ID: 39964614
The one on the top is MS SQL which I am trying to get to work and the one on the bottom is my SQL.

I am trying to convert the MySQL into MS SQL.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:WDJ98338
ID: 39964618
Yes I know the my SQL works I am trying to convert the My SQL into MS SQL to create a table and in MS SQL.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39964627
SQL Server does not support the enum data type, too many square brackets, there is one too many comma at the end & other comments below
CREATE TABLE [dbo].[Country]
      (
            [Code] char(3) NOT NULL            /* why use char instead of varchar or nvarchar ?? */
          , [Name] char(52) NOT NULL
          , [Continent] [varchar](30) NOT NULL /* enum is not supported */
          , [Region] [char](26) NOT NULL
          , [SurfaceArea] decimal(10, 2)       /* use decimal */
          , [IndepYear] smallint
          , [Population] int NOT NULL
          , [LifeExpectancy] decimal(3, 1)
          , [GNP] decimal(10, 2)
          , [GNPOld] decimal(10, 2)
          , [LocalName] char(45) NOT NULL
          , [GovernmentForm] char(45) NOT NULL
          , [HeadOfState] char(60)
          , [Capital] int
          , [Code2] char(2) NOT NULL
      )
;

Open in new window

For the enum one approach is to create a lookup table for those values (as the primary key of that table) then create a foreign key in this table to the lookup table.

or you can do something like this:
CREATE TABLE [dbo].[Country]
      (
            [Code] char(3) NOT NULL            /* why use char instead of varchar or nvarchar ?? */
          , [Name] char(52) NOT NULL
          , [Continent] [varchar](30) NOT NULL CHECK (Continent IN ('a', 'b', 'c')) /* enum is not supported */
          , [Region] [char](26) NOT NULL
          , [SurfaceArea] decimal(10, 2)       /* use decimal */
          , [IndepYear] smallint
          , [Population] int NOT NULL
          , [LifeExpectancy] decimal(3, 1)
          , [GNP] decimal(10, 2)
          , [GNPOld] decimal(10, 2)
          , [LocalName] char(45) NOT NULL
          , [GovernmentForm] char(45) NOT NULL
          , [HeadOfState] char(60)
          , [Capital] int
          , [Code2] char(2) NOT NULL
      )
;

Open in new window

ps: there's no need to add sql as a file, just use a code block
0
 

Author Closing Comment

by:WDJ98338
ID: 39964636
Thank you very much....
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question