Solved

Converting My SQL to MS SQL

Posted on 2014-03-30
6
294 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 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