Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert MySQL tables to Microsoft tables

Posted on 2014-07-22
10
Medium Priority
?
362 Views
Last Modified: 2014-07-27
Hi All,

Please advise how to manually convert below mentioned MySQL tables to Microsoft SQL Server Tables.
DROP TABLE IF EXISTS `AUTH_ROLE_FUNC_AR_REL` ;

CREATE TABLE IF NOT EXISTS `AUTH_ROLE_FUNC_AR_REL` (
    `AUTH_ROLE_ID` VARCHAR(20) NOT NULL,
    `AUTH_FUNCTION_ID` VARCHAR(20) NOT NULL,
    `AUTH_ACCESSRIGHT_ID` VARCHAR(20) NOT NULL,
    `ACTIVE` TINYINT(1) NOT NULL DEFAULT true,
    INDEX `fk_ROLE_FUNC_AR_REL_AUTH_ROLE1_idx` (`AUTH_ROLE_ID` ASC),
    PRIMARY KEY (`AUTH_ROLE_ID` , `AUTH_FUNCTION_ID` , `AUTH_ACCESSRIGHT_ID`),
    INDEX `fk_AUTH_ROLE_FUNC_AR_REL_AUTH_FUNCTION1_idx` (`AUTH_FUNCTION_ID` ASC),
    INDEX `fk_AUTH_ROLE_FUNC_AR_REL_AUTH_ACCESSRIGHT1_idx` (`AUTH_ACCESSRIGHT_ID` ASC),
    CONSTRAINT `fk_ROLE_FUNC_AR_REL_AUTH_ROLE1` FOREIGN KEY (`AUTH_ROLE_ID`)
        REFERENCES `AUTH_ROLE` (`ID`)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_AUTH_ROLE_FUNC_AR_REL_AUTH_FUNCTION1` FOREIGN KEY (`AUTH_FUNCTION_ID`)
        REFERENCES `AUTH_FUNCTION` (`ID`)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_AUTH_ROLE_FUNC_AR_REL_AUTH_ACCESSRIGHT1` FOREIGN KEY (`AUTH_ACCESSRIGHT_ID`)
        REFERENCES `AUTH_ACCESSRIGHT` (`ID`)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=XYZ1;

Open in new window

DROP TABLE IF EXISTS `USER` ;

CREATE TABLE IF NOT EXISTS `USER` (
    `ID` VARCHAR(20) NOT NULL,
    `USERNAME` VARCHAR(50) NOT NULL,
    `PASSWORD` VARCHAR(100) NULL,
    `DISPLAYNAME` VARCHAR(60) NOT NULL,
    `PARENT_ID` VARCHAR(20) NULL DEFAULT NULL,
    `DESIGNATION` VARCHAR(45) NULL,
    `EXTERNAL` TINYINT(1) NOT NULL DEFAULT false,
    `ACTIVE` TINYINT(1) NOT NULL DEFAULT true,
    PRIMARY KEY (`ID`),
    INDEX `fk_USER_USER1_idx` (`PARENT_ID` ASC),
    CONSTRAINT `fk_USER_USER1` FOREIGN KEY (`PARENT_ID`)
        REFERENCES `USER` (`ID`)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=XYZ1;

Open in new window

0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 668 total points
ID: 40211318
sql server doesn't have the IF NOT EXISTS.
the `xxx` needs to be [xxx]  (or xxx, but as you have used reserved keywords, better keep [ ]  )

tinyint(x) needs to be tinyint

true  needs to be 1
false  needs to be 0

ENGINE=XYZ1   clauses needs to be removed

the rest seems like it should work as is...
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 664 total points
ID: 40211417
The IF EXISTS can be rewritten like this...
IF EXISTS (SELECT name FROM sys.tables WHERE name='AUTH_ROLE_FUNC_AR_REL') 
   DROP TABLE AUTH_ROLE_FUNC_AR_REL

CREATE TABLE AUTH_ROLE_FUNC_AR_REL
   -- The rest of the code here

Open in new window

>    PRIMARY KEY (`ID`),
Should go at the end of the ID column, like this (also, why is a varchar the primary key?
    `ID` VARCHAR(20) NOT NULL PRIMARY KEY,

Open in new window

Looks like angelIII caught the rest.
0
 

Author Comment

by:sqldba2013
ID: 40211609
Thanks.

I've modified tables and I got below error. Please correct me.
Error:
Msg 1018, Level 15, State 1, Line 7
Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'ACTION'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'ACTION'.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'ACTION'.
IF EXISTS (SELECT name FROM sys.tables WHERE name='AUTH_ROLE_FUNC_AR_REL') 
DROP TABLE AUTH_ROLE_FUNC_AR_REL
GO
CREATE TABLE AUTH_ROLE_FUNC_AR_REL 
(
    [AUTH_ROLE_ID] VARCHAR(20) NOT NULL,
    [AUTH_FUNCTION_ID] VARCHAR(20) NOT NULL,
    [AUTH_ACCESSRIGHT_ID] VARCHAR(20) NOT NULL,
    [ACTIVE] TINYINT NOT NULL DEFAULT 1,
    INDEX [fk_ROLE_FUNC_AR_REL_AUTH_ROLE1_idx] ([AUTH_ROLE_ID] ASC),
    PRIMARY KEY ([AUTH_ROLE_ID] , [AUTH_FUNCTION_ID], [AUTH_ACCESSRIGHT_ID]),
    INDEX [fk_AUTH_ROLE_FUNC_AR_REL_AUTH_FUNCTION1_idx] ([AUTH_FUNCTION_ID] ASC),
    INDEX [fk_AUTH_ROLE_FUNC_AR_REL_AUTH_ACCESSRIGHT1_idx] ([AUTH_ACCESSRIGHT_ID] ASC),
    CONSTRAINT [fk_ROLE_FUNC_AR_REL_AUTH_ROLE1] FOREIGN KEY ([AUTH_ROLE_ID])
    REFERENCES [AUTH_ROLE] ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [fk_AUTH_ROLE_FUNC_AR_REL_AUTH_FUNCTION1] FOREIGN KEY ([AUTH_FUNCTION_ID])
    REFERENCES [AUTH_FUNCTION] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [fk_AUTH_ROLE_FUNC_AR_REL_AUTH_ACCESSRIGHT1] FOREIGN KEY ([AUTH_ACCESSRIGHT_ID])
    REFERENCES [AUTH_ACCESSRIGHT] ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION
);

Open in new window

Error:
Msg 1018, Level 15, State 1, Line 11
Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'ACTION'.
IF EXISTS (SELECT name FROM sys.tables WHERE name='[USER]') 
DROP TABLE [USER]
GO
CREATE TABLE [USER] (
    ID INT NOT NULL,
    [USERNAME] VARCHAR(50) NOT NULL,
    [PASSWORD] VARCHAR(100) NULL,
    [DISPLAYNAME] VARCHAR(60) NOT NULL,
    [PARENT_ID] VARCHAR(20) NULL DEFAULT NULL,
    [DESIGNATION] VARCHAR(45) NULL,
    [EXTERNAL] TINYINT NOT NULL DEFAULT 0,
    [ACTIVE] TINYINT NOT NULL DEFAULT 1,
    PRIMARY KEY ([ID]),
    INDEX [fk_USER_USER1_idx] ([PARENT_ID] ASC),
    CONSTRAINT [fk_USER_USER1] FOREIGN KEY ([PARENT_ID])
    REFERENCES [USER] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
);

Open in new window

0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 668 total points
ID: 40212571
First table:

IF OBJECT_ID('AUTH_ROLE_FUNC_AR_REL') IS NOT NULL
    DROP TABLE AUTH_ROLE_FUNC_AR_REL ;

CREATE TABLE dbo.AUTH_ROLE_FUNC_AR_REL (
    AUTH_ROLE_ID varchar(20) NOT NULL,
    AUTH_FUNCTION_ID varchar(20) NOT NULL,
    AUTH_ACCESSRIGHT_ID varchar(20) NOT NULL,
    ACTIVE tinyint NOT NULL DEFAULT 1,
    PRIMARY KEY (AUTH_ROLE_ID , AUTH_FUNCTION_ID , AUTH_ACCESSRIGHT_ID),
    CONSTRAINT fk_ROLE_FUNC_AR_REL_AUTH_ROLE1 FOREIGN KEY (AUTH_ROLE_ID)
        REFERENCES AUTH_ROLE (ID)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT fk_AUTH_ROLE_FUNC_AR_REL_AUTH_FUNCTION1 FOREIGN KEY (AUTH_FUNCTION_ID)
        REFERENCES AUTH_FUNCTION (ID)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT fk_AUTH_ROLE_FUNC_AR_REL_AUTH_ACCESSRIGHT1 FOREIGN KEY (AUTH_ACCESSRIGHT_ID)
        REFERENCES AUTH_ACCESSRIGHT (ID)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE NONCLUSTERED INDEX fk_ROLE_FUNC_AR_REL_AUTH_ROLE1_idx ON dbo.AUTH_ROLE_FUNC_AR_REL (AUTH_ROLE_ID ASC)
CREATE NONCLUSTERED INDEX fk_AUTH_ROLE_FUNC_AR_REL_AUTH_FUNCTION1_idx ON dbo.AUTH_ROLE_FUNC_AR_REL (AUTH_FUNCTION_ID ASC)
CREATE NONCLUSTERED INDEX fk_AUTH_ROLE_FUNC_AR_REL_AUTH_ACCESSRIGHT1_idx ON dbo.AUTH_ROLE_FUNC_AR_REL (AUTH_ACCESSRIGHT_ID ASC)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40212578
Second table:


IF OBJECT_ID([USER]) IS NOT NULL
    DROP TABLE [USER]

CREATE TABLE [USER] (
    ID varchar(20) NOT NULL,
    USERNAME varchar(50) NOT NULL,
    PASSWORD varchar(100) NULL,
    DISPLAYNAME varchar(60) NOT NULL,
    PARENT_ID varchar(20) NULL DEFAULT NULL,
    DESIGNATION varchar(45) NULL,
    [EXTERNAL] tinyint NOT NULL DEFAULT 0,
    ACTIVE tinyint NOT NULL DEFAULT 0,
    PRIMARY KEY (ID),
    CONSTRAINT fk_USER_USER1 FOREIGN KEY (PARENT_ID)
        REFERENCES [USER] (ID)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE NONCLUSTERED INDEX fk_USER_USER1_idx ON [USER] (PARENT_ID ASC)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40212582
I have no idea what:

ENGINE=XYZ1;

means.  SQL Server does not have that clause directly, so you would need to verify if/what SQL Server option replaces that.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40215781
MySQL's engine feature has no relevance in MS SQL as far as I can see.

the "Engine" options are  MyISAM, Memory, InnoDB, Archive, NDB which provide different capabilities eg:
		        MyISAM	Memory	InnoDB	Archive	NDB
Storage limits	    	256TB	RAM	64TB	None	384EB
Transactions	    	No	No	Yes	No	Yes
Locking granularity	Table	Table	Row	Table	Row

Open in new window

for more see
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40215802
@Scott surprised there's no suggestion on clustered indexing (to my knowledge there's no equivalent in MySQL)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40217028
In SQL Server, the PK will be the clustered index by default.  I have no data with which to suggest a different clustering key(s).
0
 

Author Closing Comment

by:sqldba2013
ID: 40223428
Thanks to all for your help/suggestion.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

670 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