• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
PortletPaulCommented:
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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