We'd like to create the MySQL database structure needed to populate with
the csv data contained in http://preview.tinyurl.com/airmen-mssql2mysql

All the csv data and database structure documentation are at the above
link.

The sql statements to create the needed tables are written for mssql, but
we're hoping for some help or suggestions with translating the statements
to MySQL syntax with a minimum of trouble.

Will anyone suggest please which freely-available tool they prefer, or
perhaps post a sed file that will make the needed changes?

Certainly many others must have already met the need? Thank you.

On 29-01-11 17:47, Greg Russell wrote:
conversion by hand is a simple task for this 4 tables....

--
Luuk

On 2011-01-29 17:47, Greg Russell wrote:
I googled on "migrating mssql mysql freeware" and found:

http://dev.mysql.com/tech-resources/...microsoft.html

There is a migration forum at:

http://forums.mysql.com/#f59


I recently created a datamodel for a small project in mssql and every
token in the ddl seems to be quoted with [...] making it very difficult
to read. Piping it through:

sed -e "s/\[//g" -e "s/\]//g"

helps ;-)

/Lennart

On Sat, 29 Jan 2011 18:58:34 +0100, Luuk wrote:


$ wc -l ./eadmspub.sql ; \
grep -ci "create table" ./eadmspub.sql; \
grep -ci "alter table" ./eadmspub.sql
718 ./eadmspub.sql
20
28

Not sure how you arrive at the number "4 tables", but thanks for your
reply. My concern is with the syntax differences between the 2 dialects,
as seen in the documentation at the above link, e.g.:

CREATE TABLE [dbo].[eADMSPUB_DataDictionary] (
[Category of Data] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Table] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Column] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ct_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[code_iaids] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[meaning] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Data Type eADMS] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Length eADMS] [float] NULL ,
[short_desc] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Question_Def] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Code meaning] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[typeofchange] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Change_notes] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
...
CREATE TABLE [dbo].[narratives] (
[ev_id] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Aircraft_Key] [int] NOT NULL ,
[narr_accp] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[narr_accf] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[narr_cause] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[narr_inc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lchg_date] [datetime] NULL ,
[lchg_userid] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
...
ALTER TABLE [dbo].[injury] ADD
CONSTRAINT [DF__injury__lchg_dat__0FCC5F4C] DEFAULT (getdate())
FOR [lchg_date],
CONSTRAINT [DF__injury__lchg_use__10C08385] DEFAULT (user_name
(null)) FOR [lchg_userid],
CONSTRAINT [PK_injury] PRIMARY KEY NONCLUSTERED
(
[ev_id],
[Aircraft_Key],
[inj_person_category],
[injury_level]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[engines] ADD
CONSTRAINT [PK_engines] PRIMARY KEY NONCLUSTERED
(
[ev_id],
[Aircraft_Key],
[eng_no]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dt_aircraft] ADD
CONSTRAINT [DF__dt_aircra__lchg___69A6B664] DEFAULT (getdate())
FOR [lchg_date],
CONSTRAINT [DF__dt_aircra__lchg___6A9ADA9D] DEFAULT (user_name
(null)) FOR [lchg_userid],
CONSTRAINT [PK_dt_aircraft] PRIMARY KEY NONCLUSTERED
(
[ev_id],
[Aircraft_Key],
[col_name],
[code]
) ON [PRIMARY]
GO
....


etc. etc.

On 30-01-11 18:57, Greg Russell wrote:
Oh, i was just looking at the PDF "HelpComm.pdf" and saw only about 4
tables, maybe i was too lazy to read all 8 pages in detail

Luckily for you Lennart gave a good suggestion to this too

And for the rest i can only say that i was planning on 'creating' a tool
to do something like that, because i needed the same thing, converting a
MSSQL to MYSQL, but someone else did the conversion before i got started

anyway this link is mentioned 'everywhere'
http://www.kofler.cc/mysql/mssql2mysql.html
but it moved to:
http://kofler.info/english/mssql2mysql/

Unfortunatly this does not support foreign keys..


--
Luuk