Re: MySQL to SQL
- From: SQLcat <SQLcat@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 6 Mar 2006 09:14:45 -0800
I believe you're on the right track. How does this VBScript recognize the
MySQL.sql script to do the compare?
"Alex K. Angelopoulos" wrote:
.
"SQLcat" <SQLcat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F84BEF9E-0A14-4876-B470-1E52ACA8AA08@xxxxxxxxxxxxxxxx
Here's a sampling that should help:
If the items below are being pretty representative, you're creating an
MS-SQL db on-the-fly from straight from an arbitrary MySQL db, correct? And
is it all in a single exported .sql-type file?
Anyway, below - after some comments on what you've said about data changes -
is a first generic cut on some code that will make changes automatically.
Depending on how large the data set is, it may be worthwhile handling the
table structure transformations separately, so it won't be necessary to
process all of those INSERT statements that will need fewer changes. You'll
probably find some things I've done do NOT make sense based on the details
of how you're working, so comment on them please. :)
The data types mediumtext would need to be changed to text....
Demoed below; that one is easy. I assume you're keeping the nvarchar(x)
items as-is?
CREATE INDEX, PRIMARY KEY, etc. statements are needed...
It looks like your CREATE TABLE statements already include the primary key
declarations. With the CREATE INDEX items, could you show what you are
doing now? I assume you are probably doing this as a separate step now using
ALTER TABLE statements after scanning the tables for *_IDX_# columns?
... MySql specifies length with bigint, int, etc. that needs to be
removed.
Er - removed? Or do you just mean they should all be changed to SQL int or
bigint without the size spec following them? I assume this IS what you mean,
and in the code demo I change bigint([0-9]+) to just bigint and int([0-9]+)
to just int.
... I do a find \' and replace with '' in
the INSERT statements (not for all tables) which will usually be in a name
e.g. o\'connell which should read o''connell.
For the moment, I'm skipping the INSERT changes so the code focuses on the
table setup syntax alterations.
In the demo below, I assume that the MySQL stuff is exported to a file -
call it my.sql - and I'll further assume that we're going to save the
transformed data over into ms.sql. You would run it with a command line
something like this, assuming you save the VBScript code as my2ms.vbs:
cscript my2ms.vbs < my.sql > ms.sql
The reason I make a big deal out of it is that I saved time and script
effort by setting this up to use the console streams for I/O. :)
' CUT HERE
' my2ms.vbs
Do While Not WScript.StdIn.AtEndofStream
Dim data: data = WScript.StdIn.ReadLine
WScript.StdOut.WriteLine MySqlToMsSqlLine(data)
Loop
Function MySqlToMsSqlLine(ByVal line)
Dim rx: Set rx = new RegExp
' change mediumtext to text
rx.Pattern = "\bmediumtext\b"
line = rx.Replace(line, "text")
rx.Pattern = "\bbigint\([0-9]+\)"
line = rx.Replace(line, "bigint")
rx.Pattern = "\bint\([0-9]+\)"
line = rx.Replace(line, "int")
MySqlToMsSqlLine = line
End Function
' end of script
' CUT HERE
This is actually a very crude but workable way of transforming the table
declarations when no reordering is necessary - all we are doing here is
looking for specific words that need to be "remapped" to work in MS-SQL.
Reordering is also possible if necessary with well-designed capturing
statements. As-is, this will perform the following changes on the input
file:
mediumtext -> text
bigint(#) -> bigint
int(#) -> int
Everything else is passed through unchanged.
What may be a good move at this point is to apply the script to an export
set and see what table creation lines still need to be changed.
- References:
- Re: MySQL to SQL
- From: SQLcat
- Re: MySQL to SQL
- Prev by Date: Re: MySQL to SQL
- Next by Date: Re: Script help, reading value from HKEY_CURRENT_USER remotley
- Previous by thread: Re: MySQL to SQL
- Next by thread: Re: MySQL to SQL
- Index(es):
Relevant Pages
|
