Ms sql server migration to mysql.

phirez

I am attempting to migrate 84 tables located in ms-sql to mysql using the mysql migration toolkit. In all of these tables there is a datetime column. The problem is MS-SQL server's Datetime column can hold milliseconds. Mysql's DATETIME column can not hold milliseconds. The obvious solution is to create a seprate interger or varchar field that holds the millisecond data. So I created a SQL script (Bottom of topic) that creates a virtual column called "millisec". Is there any way to execute the sql script on the migration toolkit's source database before the the migration toolkit executes the "creates.sql" script on the target database. If not is there another solution to my problem.
SQL script:
DECLARE @today datetime, @yesterday datetime
SELECT @today = CAST (SUBSTRING(CONVERT(varchar,GETUTCDate(),120),1,10) + ' 00:00:00' AS datetime)
SELECT @yesterday = DATEADD(day, -1, @today)
SELECT
CONVERT(char(19),time_tag,120) AS time_tag,
substring(CONVERT(char(23),time_tag,121),21,3) AS millisec,
S1_QUAL_FLAG,
S1_COUNT,
S1_COUNT_RATE,
S2_QUAL_FLAG,
S2_COUNT,
S2_COUNT_RATE,
S3_QUAL_FLAG,
S3_COUNT,
S3_COUNT_RATE,
S4_QUAL_FLAG,
S4_COUNT,
S4_COUNT_RATE,
S5_QUAL_FLAG,
S5_COUNT,
S5_COUNT_RATE
FROM thetablename
WHERE time_tag >= @yesterday and time_tag < @today
ORDER BY time_tag

aprelium

phirez,
If this is not possible using the migration tool (you might want to post that question on mysql.com forums), we suggest that you make copies of the tables to migrate in MS-SQL, next run your SQL script you cited on the copies, finally migrate the resulting tables to MySQL.

wonn1377

I think u should use a third party help to resolve this problem, it can migrate almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc.i use dbload to solve it when i was migrated my data, i found it on google search.
Download Free : http://www.dbload.com

DonQuichote

There are basically two options:
- Live with the decreased accuracy
- Use another data type.
The other data type could be a string (VARCHAR). If you extend the normal datetime format with decimals, you have the possibility of indexing and you can use the existing datetime functions after applying SUBSTR() to it:
'2009-08-21 12:33:25.0346' could be a value. The calculus becomes a bit harder, but it can be done.
You could use a DOUBLE type and work the dates to a unix timestamp. Some programming languages can deal with that type in an easier way. Again, indexes should work as expected.

jean3167

Here is a tool to convert MSSQL to MYSQL database. That i found on google search it this converter can do migrate any data try and tel me is that worth or not.

radjiv

Hi! I need to convert database from MS SQL to MySQL, not only data and indexes but queries/views as well. As can I see from vendor's websites none of the tools above can handle it. I found this one, vendor says it support views, but this feature available in full (paid) version only:
http://www.convert-in.com/mss2sql.htm
Does anybody have positive experience of converting views with that tool?

DonQuichote

As far as I know, the latest MariaDB can work with microseconds.
See https://mariadb.com/kb/en/mariadb-versus-mysql-features/
This means installing another database program, but it is a drop-in replacement for MySQL. If you really want microseconds, you might try that. Note, however, that most providers will offer MySQL rather than MariaDB.