mysql to ms sql

Need a little help. I am a newbie with both databases (mysql and mssql). I
am transferring from mysql to ms sql. There are very few tables and data.
I am basically transferring structure by hand.
I am finding the syntax to be difficult to deal with. I have a txt file
with several tables and their attributes from mysql but I am not finding the
exact same thing in ms sql. I wonder if someone can look at the code and
give me a hand. I am using the EM to put this on ms sql online.
Here is an example of one table and the trouble I am having with it. This
is the mysql code to be changed. Please see my comments under this code.
CREATE TABLE room_action (
id varchar(100) NOT NULL default '',
room tinyint(3) unsigned NOT NULL default '0',
user tinyint(3) unsigned NOT NULL default '0',
name varchar(50) NOT NULL default '',
cmd varchar(10) NOT NULL default '',
x mediumint(9) defaulnamet '0',
y mediumint(9) default '0',
msg varchar(255) default NULL,
avatar tinyint(3) unsigned default NULL,
viewed tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
I am creating the table in the EM. I am assuming that the numbers are the
field length. For example:
room tinyint(3) unsigned NOT NULL default '0'
I am attempting to put 3 in the length but it will only use 1.
I also am confused about unsigned. Also what is TYPE=MyISAM
Can anyone give me a hand?
Thanks
Laura KLaura,
Try this:
CREATE TABLE room_action (
id varchar(100) NOT NULL default '',
room smallint NOT NULL default 0,
userID smallint NOT NULL default 0, --user is a keyword, change to
userID
name varchar(50) NOT NULL default '',
cmd varchar(10) NOT NULL default '',
x integer default 0,
y integer default 0,
msg varchar(255) default NULL,
avatar smallint default NULL, --use a default of 0 instead of null if
possible, will prevent aggreatation warnings
viewed smallint NOT NULL default 0,
PRIMARY KEY (id)
)
Also, please consider removing the default '' on your not null columns. It
is contradictory and a pain in the rear to maintain.
Take some time to review Books On Line, especially data types, as many of
your conversion questions will be answered.
--Morgan
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:%23Gz90CleDHA.1824@.TK2MSFTNGP10.phx.gbl...
> Need a little help. I am a newbie with both databases (mysql and mssql).
I
> am transferring from mysql to ms sql. There are very few tables and data.
> I am basically transferring structure by hand.
> I am finding the syntax to be difficult to deal with. I have a txt file
> with several tables and their attributes from mysql but I am not finding
the
> exact same thing in ms sql. I wonder if someone can look at the code and
> give me a hand. I am using the EM to put this on ms sql online.
> Here is an example of one table and the trouble I am having with it. This
> is the mysql code to be changed. Please see my comments under this code.
> CREATE TABLE room_action (
> id varchar(100) NOT NULL default '',
> room tinyint(3) unsigned NOT NULL default '0',
> user tinyint(3) unsigned NOT NULL default '0',
> name varchar(50) NOT NULL default '',
> cmd varchar(10) NOT NULL default '',
> x mediumint(9) defaulnamet '0',
> y mediumint(9) default '0',
> msg varchar(255) default NULL,
> avatar tinyint(3) unsigned default NULL,
> viewed tinyint(3) unsigned NOT NULL default '0',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
> I am creating the table in the EM. I am assuming that the numbers are the
> field length. For example:
> room tinyint(3) unsigned NOT NULL default '0'
> I am attempting to put 3 in the length but it will only use 1.
> I also am confused about unsigned. Also what is TYPE=MyISAM
> Can anyone give me a hand?
> Thanks
> Laura K
>|||Thanks but I don't want to do mySQL. The original program was written for a
mysql database but we want to run it from MS sql. I am writing the tables
by hand in ms sql but I do not know much about the differences.
Laura
"Groucho" <rog11228@.aol.com> wrote in message
news:eFKpYqleDHA.4024@.TK2MSFTNGP11.phx.gbl...
> "Laura K" wrote in message
> > Need a little help. I am a newbie with both databases (mysql and
mssql).
> .
> .
> Don't expect to much help in the way of MySql here.
> It's not considered a *real* rdbms in these parts:)
> There are many differences/similarities between the two.
> You'll get Mysql help on google at:
> mailing.database.mysql
> mailing.database.mysql-win32
> You can also let MySql utilities help you,especially the ones that
> import and export data.Check out:
> SQLyog @.
> www.webyog.com
> and
> MySql GUI tool @.
> www.mysqlgui.net
> Install the MySql odbc driver and you can use the
> Server dts utility to transfer data and see how the
> table structure(s) turn out.
> A type MyISAM is a particular type of MySql table.
> In MySql there are different types of tables you can use.
> This concept does not exist in Server.See the MySql
> docs for details.
> It also matters what version of MySql your working with.
> Check their website for details.(You should be using 4.0.xx).
> If you want subqueries,derived tables and some other
> fancy server like constructs check out 4.1 alpha.
> Using both Server and MySql will probably become
> pretty common as time goes on.You can pick the one
> you want to pay for:).
> RAC v2.2 and QALite for Sql Server released.
> www.rac4sql.net
>
>|||Thanks so much. I am beginning the understand the differences.
I did a search for conversion but all I got all kinds of non helpful things
instead from the search engines. I will take a look at books online.
Thanks again for the detailed help.
Laura
"Morgan" <mfears@.spamcop.net> wrote in message
news:OaAQiGmeDHA.3528@.tk2msftngp13.phx.gbl...
> Laura,
> Try this:
> CREATE TABLE room_action (
> id varchar(100) NOT NULL default '',
> room smallint NOT NULL default 0,
> userID smallint NOT NULL default 0, --user is a keyword, change to
> userID
> name varchar(50) NOT NULL default '',
> cmd varchar(10) NOT NULL default '',
> x integer default 0,
> y integer default 0,
> msg varchar(255) default NULL,
> avatar smallint default NULL, --use a default of 0 instead of null if
> possible, will prevent aggreatation warnings
> viewed smallint NOT NULL default 0,
> PRIMARY KEY (id)
> )
> Also, please consider removing the default '' on your not null columns. It
> is contradictory and a pain in the rear to maintain.
> Take some time to review Books On Line, especially data types, as many of
> your conversion questions will be answered.
> --Morgan
>
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23Gz90CleDHA.1824@.TK2MSFTNGP10.phx.gbl...
> > Need a little help. I am a newbie with both databases (mysql and
mssql).
> I
> > am transferring from mysql to ms sql. There are very few tables and
data.
> > I am basically transferring structure by hand.
> >
> > I am finding the syntax to be difficult to deal with. I have a txt file
> > with several tables and their attributes from mysql but I am not finding
> the
> > exact same thing in ms sql. I wonder if someone can look at the code
and
> > give me a hand. I am using the EM to put this on ms sql online.
> >
> > Here is an example of one table and the trouble I am having with it.
This
> > is the mysql code to be changed. Please see my comments under this code.
> >
> > CREATE TABLE room_action (
> > id varchar(100) NOT NULL default '',
> > room tinyint(3) unsigned NOT NULL default '0',
> > user tinyint(3) unsigned NOT NULL default '0',
> > name varchar(50) NOT NULL default '',
> > cmd varchar(10) NOT NULL default '',
> > x mediumint(9) defaulnamet '0',
> > y mediumint(9) default '0',
> > msg varchar(255) default NULL,
> > avatar tinyint(3) unsigned default NULL,
> > viewed tinyint(3) unsigned NOT NULL default '0',
> > PRIMARY KEY (id)
> > ) TYPE=MyISAM;
> >
> >
> > I am creating the table in the EM. I am assuming that the numbers are
the
> > field length. For example:
> > room tinyint(3) unsigned NOT NULL default '0'
> >
> > I am attempting to put 3 in the length but it will only use 1.
> >
> > I also am confused about unsigned. Also what is TYPE=MyISAM
> >
> > Can anyone give me a hand?
> >
> > Thanks
> >
> > Laura K
> >
> >
>