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.
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 |
|
|
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. |
|
|
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 |
|
|
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 IDENTITYDates 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 statementENGINE=MyISAM Remove this, not relevantDEFAULT CHARSET=latin1 I think you need to do this on EVERY varchar/char datatype column definition using the COLLATE syntaxAUTO_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 |
|
|
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. |
|
|
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 |
|
|
ppatil
Starting Member
9 Posts |
Posted - 2006-08-02 : 07:09:16
|
Dear Kristen,Thanks a lot.Regards,Prathamesh |
|
|
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 IDENTITYDates 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 statementENGINE=MyISAM Remove this, not relevantDEFAULT CHARSET=latin1 I think you need to do this on EVERY varchar/char datatype column definition using the COLLATE syntaxAUTO_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! |
|
|
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 |
|
|
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! |
|
|
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 |
|
|
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! |
|
|
|
|
|
|
|