SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to run Mysql create table script into MsSQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ppatil
Starting Member

9 Posts

Posted - 08/02/2006 :  02:44:49  Show Profile  Reply with Quote
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)

Singapore
17635 Posts

Posted - 08/02/2006 :  02:53:51  Show Profile  Reply with Quote
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 - 08/02/2006 :  02:57:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/02/2006 :  03:09:49  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 08/02/2006 :  03:38:49  Show Profile  Reply with Quote
"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

Edited by - Kristen on 08/02/2006 03:40:40
Go to Top of Page

ppatil
Starting Member

9 Posts

Posted - 08/02/2006 :  04:59:50  Show Profile  Reply with Quote
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)

Singapore
17635 Posts

Posted - 08/02/2006 :  05:03:15  Show Profile  Reply with Quote
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 - 08/02/2006 :  07:09:16  Show Profile  Reply with Quote
Dear Kristen,

Thanks a lot.

Regards,
Prathamesh
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 08/02/2006 :  08:39:30  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 08/02/2006 :  10:38:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 08/02/2006 :  11:18:23  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 08/02/2006 :  11:29:38  Show Profile  Reply with Quote
"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
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 08/02/2006 :  12:02:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000