Re: MySQL to SQL
- From: SQLcat <SQLcat@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 7 Mar 2006 07:49:28 -0800
also, using your code:
rx.Pattern = "\bBIGINT\([0-9]+\)"
line = rx.Replace(line, "BIGINT")
I'm trying to remove the ) TYPE=InnoDB; so it either disappears or is
commented out like this:
-- )TYPE=InnoDB;
"SQLcat" wrote:
Awesome. That cleaned up the datatypes. Please see the TSQL below:.
CREATE TABLE REPORT_COMPUTER (
ACQUISITION_DATE datetime default NULL,
ACQUISITION_METHOD_DESCRIPTION varchar(200) default NULL,
ACQUISITION_METHOD_NAME varchar(50) default NULL,
ADDRESS varchar(200) default NULL,
ASSET_TAG varchar(64) default NULL,
BIOS_MANUFACTURER varchar(100) default NULL,
BIOS_RELEASE_DATE datetime default NULL,
BIOS_VERSION varchar(100) default NULL,
BUILDING varchar(50) default NULL,
COST float NOT NULL default '0',
DEPARTMENT varchar(64) default NULL,
FINANCIAL_DEPARTMENT varchar(64) default NULL,
FINANCIAL_NOTES text,
HOST_NAME varchar(64) default NULL,
ID varchar(14) NOT NULL default '',
LEASE_RENEWAL_DATE datetime default NULL,
LOCATION varchar(64) default NULL,
MANUFACTURER varchar(64) default NULL,
MAUID varchar(20) default NULL,
MEMORY_SIZE bigint NOT NULL default '0',
MODEL varchar(255) default NULL,
NUM_PROCESSORS int NOT NULL default '0',
PORT varchar(10) default NULL,
PRIMARY_USER_ID varchar(14) default NULL,
PURCHASE_ORDER varchar(15) default NULL,
RETIREMENT_DATE datetime default NULL,
RETIREMENT_METHOD_DESCRIPTION varchar(200) default NULL,
RETIREMENT_METHOD_NAME varchar(50) default NULL,
ROOM varchar(50) default NULL,
SERIAL_NUMBER varchar(64) default NULL,
STATUS varchar(50) default NULL,
SYSTEM_NAME varchar(64) default NULL,
CUSTOM_TEXT_FIELD1 text,
CUSTOM_TEXT_FIELD2 text,
CUSTOM_TEXT_FIELD3 text,
CUSTOM_TEXT_FIELD4 text,
CUSTOM_TEXT_FIELD5 text,
CUSTOM_DATE_FIELD1 datetime default NULL,
CUSTOM_DATE_FIELD2 datetime default NULL,
CUSTOM_DATE_FIELD3 datetime default NULL,
PRIMARY KEY (ID),
KEY REPORT_COMPUTER_IDX_1 (MAUID),
KEY REPORT_COMPUTER_IDX_2 (SYSTEM_NAME),
KEY REPORT_COMPUTER_IDX_3 (PRIMARY_USER_ID)
) TYPE=InnoDB;
HOST_NAME will need to have brackets around it. Columns like that will need
to be identified somehow in the script. Also, the last three columns need
the correct syntax for index key creation. And, the ID column needs to have
the PK properly defined in TSQL. Honestly though, I've already got the table
structures created as they will not change. It's the INSERT statements that
need the tweaking (find \' replace with ''). An example of this would be
below:
INSERT INTO REPORT_SOFTWARE_CHANGE_ITEM VALUES
(949978046498856,'01r-000022-001','Scan','2005-11-10','ADDITION','InterVideo
WinDVD','4.0-B11.270','InterVideo Inc.');
INSERT INTO REPORT_SOFTWARE_CHANGE_ITEM VALUES
(949978046498857,'01r-000022-001','Scan','2005-11-10','REMOVAL','Bytemobile',NULL,NULL);
INSERT INTO REPORT_SOFTWARE_CHANGE_ITEM VALUES
(949978046498858,'01r-000022-001','Scan','2005-11-10','REMOVAL','Alex\'s Ftp
Server',NULL,NULL);
INSERT INTO REPORT_SOFTWARE_CHANGE_ITEM VALUES
(949978046498859,'01r-000048-001','Scan','2006-01-09','ADDITION','Microsoft
Office 2000 SR-1 Professional','9.00.9327','Microsoft, Nortel Networks');
INSERT INTO REPORT_SOFTWARE_CHANGE_ITEM VALUES
(949978046498860,'01r-000048-001','Scan','2006-01-09','ADDITION','Sonic
Update Manager','2.9','Sonic Solutions');
INSERT INTO REPORT_SOFTWARE_CHANGE_ITEM VALUES
(949978046498861,'01r-000048-001','Scan','2006-01-09','ADDITION','ATI Control
Panel','6.14.10.5043',NULL);
INSERT INTO REPORT_SOFTWARE_CHANGE_ITEM VALUES
(949978046498862,'01r-000048-001','Scan','2006-01-09','ADDITION','IBM
ThinkVantage Technologies Welcome Message','1.01',NULL);
INSERT INTO REPORT_SOFTWARE_CHANGE_ITEM VALUES
(949978046498863,'01r-000048-001','Scan','2006-01-09','ADDITION','IBM Rescue
and Recovery with Rapid Restore','1.00.0033','IBM Corporation');
'Alex\'s FTP Server' would need to be changed to 'Alex''s FTP Server'
"Alex K. Angelopoulos" wrote:
You're missing the redirection operators in that. it should look like this -
all one line.
cscript "C:\temp\logs\my2ms.vbs" < "C:\temp\dir\file.sql" >
"C:\temp\test.sql"
"SQLcat" <SQLcat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E61AEF8E-FC2F-4A9C-8F47-93EDAEB43D42@xxxxxxxxxxxxxxxx
Thias is what I get. It doesn't do anything further but display the
below:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\username>cscript "C:\temp\logs\my2ms.vbs"
C:\temp\dir\file.sql" "C:\temp\test.sql"
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
"Alex K. Angelopoulos" wrote:
It assumes. :)
Just plug in the name of the MySQL script as the input element. If you're
not a console user at all, here's a really length example complete with
paths using embedded spaces. Suppose the VBScript is saved as "C:\Temp
Files\My2Ms.vbs", the MySQL source script is "K:\Some place\MySQL.sql",
and
you want to save the output for MS-SQL use as "S:\else where\ms.sql". You
would do this at a command prompt:
cscript "C:\Temp Files\My2Ms.vbs" < "K:\Some place\MySQL.sql" > "S:\else
where\ms.sql"
"SQLcat" <SQLcat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:25483C06-FAC5-4B44-BA16-EB2DFE6DBAFD@xxxxxxxxxxxxxxxx
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
- From: SQLcat
- Re: MySQL to SQL
- From: SQLcat
- Re: MySQL to SQL
- From: SQLcat
- Re: MySQL to SQL
- Prev by Date: Re: Error in CopyFolder method?
- Next by Date: Remote Scripting - To work with Virtual Server 2005
- Previous by thread: Re: MySQL to SQL
- Next by thread: Re: MySQL to SQL
- Index(es):
Relevant Pages
|
