Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to run Mysql create table script into MsSQL

Author  Topic 

ppatil
Starting Member

9 Posts

Posted - 2006-08-02 : 02:44:49
Dear All,

I have the table creation script and insret record script.
This is MySQl Format.
What changes I have to do so can I run this scripts into SQL Server 2000.
If any body has successfully done it then please tell me the procedure.

CREATE TABLE `activity` (
`id` bigint(20) NOT NULL auto_increment,
`object_type` varchar(60) default NULL,
`object_id` varchar(20) default NULL,
`person_id` bigint(20) NOT NULL default '0',
`activity_dtm` datetime NOT NULL default '0000-00-00 00:00:00',
`activity_type_cd` varchar(25) NOT NULL default '',
`description_code` varchar(200) default NULL,
PRIMARY KEY (`id`),
KEY `FK9D4BF30FB4715636` (`activity_type_cd`),
KEY `FK9D4BF30F270CDEE0` (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `activity`
--


-- --------------------------------------------------------

--
-- Table structure for table `actv_type`
--

CREATE TABLE `actv_type` (
`code` varchar(25) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`void_ind` char(1) NOT NULL default '',
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `actv_type`
--

INSERT INTO `actv_type` VALUES ('job_create', 'Created job', 'F');
INSERT INTO `actv_type` VALUES ('job_update', 'Changed job', 'F');
INSERT INTO `actv_type` VALUES ('job_void', 'Voided job', 'F');
INSERT INTO `actv_type` VALUES ('job_activate', 'Activated job', 'F');
INSERT INTO `actv_type` VALUES ('job_deactivate', 'Changed job to deactive', 'F');
INSERT INTO `actv_type` VALUES ('job_appl_create', 'Created application', 'F');
INSERT INTO `actv_type` VALUES ('job_appl_update', 'Updated application', 'F');
INSERT INTO `actv_type` VALUES ('intrv_create', 'Created interview', 'F');
INSERT INTO `actv_type` VALUES ('intrv_update', 'Updated interview', 'F');
INSERT INTO `actv_type` VALUES ('person_update', 'Update person', 'F');
INSERT INTO `actv_type` VALUES ('person_create', 'Create person', 'F');
INSERT INTO `actv_type` VALUES ('company_void', 'Voided company', 'F');
INSERT INTO `actv_type` VALUES ('company_create', 'Created company', 'F');
INSERT INTO `actv_type` VALUES ('company_update', 'Updated Company', 'F');


Thanks in Advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-02 : 02:53:51
You want some tool or utility that can convert MySQL script to MSSQL script ?

or you just need help to convert the script you posted into MSSQL ?


KH

Go to Top of Page

ppatil
Starting Member

9 Posts

Posted - 2006-08-02 : 02:57:43
Dear Khatan,


Thanks For feedback.
Can u give me one example about waht to convert in that script.

Regards,
Prathamesh.
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-02 : 03:09:49
if you are kicking off with MS-SQL Server, then best thing for creating the table is to you Enterprize manager.

for instance just open the enterprize manager, and then create the table
activity with given columns. and then from there you can create the script for the table, till the time you get use with syntax of SQL Server.

and about your insert statement. it should be somthing like this


INSERT INTO actv_type VALUES ('job_create', 'Created job', 'F')
.................
.................
.................



Chirag
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 03:38:49
"Can u give me one example about waht to convert in that script."

The back-single-quote is not permitted as a table/column name delimiter. You need to use nothing (for unreserved words) or, for safety, square brackets.

You could probably make the following global find & replace to get you started:

'{space} to ]{space}
{space}' to {space}'
') to ])
(' to )[

auto_increment needs to be changed to use IDENTITY

Dates should not have hyphens in them, and '0000-00-00 00:00:00' is an illegal date anyway!

KEY [FK9D4BF30FB4715636] ([activity_type_cd])

needs to be converted to a CREATE INDEX statement

ENGINE=MyISAM Remove this, not relevant

DEFAULT CHARSET=latin1 I think you need to do this on EVERY varchar/char datatype column definition using the COLLATE syntax

AUTO_INCREMENT=1 needs to be part of your IDENTITY statement (but 1 is the default)


CREATE TABLE [activity] (
[id] bigint(20) IDENTITY(1,1) NOT NULL,
[object_type] varchar(60) default COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[object_id] varchar(20) default COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[person_id] bigint(20) NOT NULL default '0',
-- Default date needs changing to legal date (or allows NULLs!)
[activity_dtm] datetime NOT NULL default '0000-00-00 00:00:00',
[activity_type_cd] varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL default '',
[description_code] varchar(200) default COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY ([id]),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

KEY [FK9D4BF30FB4715636] ([activity_type_cd]),

CREATE INDEX [FK9D4BF30FB4715636] ON [activity]
(
[activity_type_cd]
)

KEY [FK9D4BF30F270CDEE0] ([person_id])

CREATE INDEX [FK9D4BF30F270CDEE0] ON [activity]
(
[person_id]
)

Kristen
Go to Top of Page

ppatil
Starting Member

9 Posts

Posted - 2006-08-02 : 04:59:50
Dear Khtan,

Thanks a lot for your useful feedback.
Your suggesstion is very useful for new DBA.
Once again thanks.

Regards,
Prathamesh.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-02 : 05:03:15
quote:
Originally posted by ppatil

Dear Khtan,

Thanks a lot for your useful feedback.
Your suggesstion is very useful for new DBA.
Once again thanks.

Regards,
Prathamesh.


You are welcome. Kristen did all the work. You should thank him not me


KH

Go to Top of Page

ppatil
Starting Member

9 Posts

Posted - 2006-08-02 : 07:09:16
Dear Kristen,

Thanks a lot.

Regards,
Prathamesh
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-02 : 08:39:30
quote:
Originally posted by Kristen

"Can u give me one example about waht to convert in that script."

The back-single-quote is not permitted as a table/column name delimiter. You need to use nothing (for unreserved words) or, for safety, square brackets.

You could probably make the following global find & replace to get you started:

'{space} to ]{space}
{space}' to {space}'
') to ])
(' to )[

auto_increment needs to be changed to use IDENTITY

Dates should not have hyphens in them, and '0000-00-00 00:00:00' is an illegal date anyway!

KEY [FK9D4BF30FB4715636] ([activity_type_cd])

needs to be converted to a CREATE INDEX statement

ENGINE=MyISAM Remove this, not relevant

DEFAULT CHARSET=latin1 I think you need to do this on EVERY varchar/char datatype column definition using the COLLATE syntax

AUTO_INCREMENT=1 needs to be part of your IDENTITY statement (but 1 is the default)


CREATE TABLE [activity] (
[id] bigint(20) IDENTITY(1,1) NOT NULL,
[object_type] varchar(60) default COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[object_id] varchar(20) default COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[person_id] bigint(20) NOT NULL default '0',
-- Default date needs changing to legal date (or allows NULLs!)
[activity_dtm] datetime NOT NULL default '0000-00-00 00:00:00',
[activity_type_cd] varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL default '',
[description_code] varchar(200) default COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY ([id]),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

KEY [FK9D4BF30FB4715636] ([activity_type_cd]),

CREATE INDEX [FK9D4BF30FB4715636] ON [activity]
(
[activity_type_cd]
)

KEY [FK9D4BF30F270CDEE0] ([person_id])

CREATE INDEX [FK9D4BF30F270CDEE0] ON [activity]
(
[person_id]
)

Kristen



Just thought I'd mention that it is important to ensure you need explicit collations at the column level. IMHO, it is a LOT easier to ensure that the server and thus the DB have the correct collations, and then create the Table without specifying collations.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 10:38:15
I did the COLLATE thingie because the MySQL code had a reference to a Collation.

That said: we always use explicit collations, so that our database will continue to run fine if it gets put on a server with a different collation.

We also explicitly COLLATE all temp table definitions (char/varchar/text), and all joins with columns in external databases.

Kristen
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-02 : 11:18:23
ahh - the temp table definition collation will get around the problems...

ooi, can you (do you) explicitly specify collations on table variables?


*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 11:29:38
"do you explicitly specify collations on table variables?"

Yup (within the column definition for each char/varchar/text)

Kristen
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-02 : 12:02:34
ok, thanks for that. That does feel clean and more robust, even if it does use up a bit more of the ol' fingers during coding.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page
   

- Advertisement -