• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

Converting My SQL to MS SQL

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
WDJ98338
Asked:
WDJ98338
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
Which one is correct and working?
0
 
Dan CraciunIT ConsultantCommented:
The mysql one works. Just tested it in phpmyadmin.
0
 
WDJ98338Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
WDJ98338Author Commented:
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
 
PortletPaulfreelancerCommented:
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
 
WDJ98338Author Commented:
Thank you very much....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now