Re: MySQL to SQL

basically I want to take a MySql script and load it into a SQL database.

"SQLcat" wrote:

Here's a sampling that should help:

-- Server version 4.0.14-standard

--
-- Table structure for table 'REPORT_ASSET_END_USER'
--

CREATE TABLE REPORT_ASSET_END_USER (
ASSET_ID char(14) NOT NULL default '',
END_USER_ID char(14) NOT NULL default '',
KEY REPORT_ASSET_END_USER_IDX_1 (ASSET_ID),
KEY REPORT_ASSET_END_USER_IDX_2 (END_USER_ID)
) TYPE=InnoDB;

--
-- Dumping data for table 'REPORT_ASSET_END_USER'
--

INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00000l-02u','00t-00000g-031');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-000006-005','00t-00000d-033');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-000031-005','00t-00000d-008');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-000012-002','00t-00000g-037');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00001f-003','00t-00000g-038');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00000p-007','00t-00000g-03b');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00000p-010','00t-00000d-038');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00000p-03c','00t-00000g-03d');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-000006-00e','00t-00000g-03e');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00000p-011','00t-00000d-03a');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00000l-00e','00t-000007-000');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00000l-033','00t-00000d-03d');
INSERT INTO REPORT_ASSET_END_USER VALUES ('01r-00001f-004','00t-00000g-03h');


--
-- Table structure for table 'REPORT_COMPUTER'
--

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 mediumtext,
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(20) NOT NULL default '0',
MODEL varchar(255) default NULL,
NUM_PROCESSORS int(11) 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 mediumtext,
CUSTOM_TEXT_FIELD2 mediumtext,
CUSTOM_TEXT_FIELD3 mediumtext,
CUSTOM_TEXT_FIELD4 mediumtext,
CUSTOM_TEXT_FIELD5 mediumtext,
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;

--
-- Dumping data for table 'REPORT_COMPUTER'
--

INSERT INTO REPORT_COMPUTER VALUES
(NULL,NULL,NULL,NULL,NULL,'IBM','2004-06-18 00:00:00','1RETCDWW
(3.06f)',NULL,0,'Information
Technology',NULL,NULL,'COASVENDSENLT','01r-000000-000',NULL,'Conshohocken
(HQ)','IBM','2.232989893927041E8',511,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'No
Asset
Information','Active','COASVENDSENLT',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO REPORT_COMPUTER VALUES
(NULL,NULL,NULL,NULL,NULL,'IBM','2004-01-26 00:00:00','1RET87WW (2.14
)',NULL,0,'Information
Technology',NULL,NULL,'combachilt','01r-000000-001',NULL,'Conshohocken
(HQ)','IBM','4.312917916091594E8',511,'2378DHU',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'99BC247','Active','COMBACHILT',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO REPORT_COMPUTER VALUES
(NULL,NULL,NULL,NULL,NULL,'IBM','2004-06-18 00:00:00','1RETCDWW
(3.06f)',NULL,0,'Information
Technology',NULL,NULL,'CODMINORLT','01r-000000-003',NULL,'Conshohocken
(HQ)','IBM','4.700473533948285E8',511,'2378DWU',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'99FF440','Active','CODMINORLT',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

The data types mediumtext would need to be changed to text. CREATE INDEX,
PRIMARY KEY, etc. statements are needed. MySql specifies length with bigint,
int, etc. that needs to be removed. 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. Stuff like that. A wsh that
could scrub this script would be HUGE.

"Alex K. Angelopoulos" wrote:

"SQLcat" <SQLcat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B51B68DF-3458-407D-A7A3-279EF84C818F@xxxxxxxxxxxxxxxx
Using VBScript, is there a way to convert a MySQL script to SQL?
Currently,
I scrub the MySQL file manually to make it useable in SQL server.

You would need to write a WSH script that implements the transformation,
based on what you need to do to transform the MySQL to MS-SQL. I've done
something roughly like this before manually, and it sounds like what you're
really after is a quick filtering script that changes the syntax.

What are kinds of transformations you have to make? This is very definitely
doable, probably in a script that reads your MySQL line-by-line and tweaks
it. When I tried something similar, I seem to recall doing exactly that. I
would read the MySQL per line and look for things that were different for
MS-SQL syntax. If you can show a complete but brief example of
before-and-after, it would be easy for me to post a complete script doing
that. The cool thing is that in general, due to the nature of SQL
statements, if this is a simple export-import cycle, you will be able to see
how to extend it for almost anything else you need. :)



.

Relevant Pages

  • Re: MySQL to SQL
    ... BIOS_RELEASE_DATE datetime default NULL, ... CUSTOM_TEXT_FIELD1 mediumtext, ... I scrub the MySQL file manually to make it useable in SQL server. ... You would need to write a WSH script that implements the transformation, ...
    (microsoft.public.scripting.wsh)
  • Re: MySQL to SQL
    ... BIOS_RELEASE_DATE datetime default NULL, ... NUM_PROCESSORS int NOT NULL default '0', ... Copyright 1985-2001 Microsoft Corp. ... Microsoft Windows Script Host Version 5.6 ...
    (microsoft.public.scripting.wsh)
  • Re: Cyrus IMAP with pam_mysql?
    ... FreeBSD, but if you check the startup script, all the parameters are ... Default log directories are in the default data directory, ... Makefile for mysql) They are changed in the above script prior to ... imaps auth sufficient pam_unix.so ...
    (freebsd-questions)
  • Re: mysql - kern.maxfiles limit exceeded by uid 500 please see tuning(7)
    ... Since mysql crashes after 5 days of running your script, ... better check its operation and see if it is causing your system to exceed ...
    (comp.unix.bsd.freebsd.misc)
  • Re: migration from mysql to oracle - problems with time data type
    ... The various tables include a mysql data types datetime, ... I can change the existing time fields to timestamp and ... mysql (or have become timestamps in Oracle). ...
    (comp.databases.oracle.misc)