sqldba2013
asked on
Convert MySQL tables to Microsoft tables
Hi All,
Please advise how to manually convert below mentioned MySQL tables to Microsoft SQL Server Tables.
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;
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
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.
ENGINE=XYZ1;
means. SQL Server does not have that clause directly, so you would need to verify if/what SQL Server option replaces that.
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:
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
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
for more seehttp://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
@Scott surprised there's no suggestion on clustered indexing (to my knowledge there's no equivalent in MySQL)
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).
ASKER
Thanks to all for your help/suggestion.
ASKER
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'.
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'.
Open in new window