Ms sql server migration to mysql.

 
Post new topic   Reply to topic    Aprelium Forum Index -> Databases
View previous topic :: View next topic  
Author Message
phirez
-


Joined: 22 Apr 2006
Posts: 57

PostPosted: Mon Jul 16, 2007 6:12 pm    Post subject: Ms sql server migration to mysql. Reply with quote

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
Back to top View user's profile Send private message
aprelium
-


Joined: 22 Mar 2002
Posts: 6800

PostPosted: Mon Jul 16, 2007 10:06 pm    Post subject: Re: Ms sql server migration to mysql. Reply with quote

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.
_________________
Support Team
Aprelium - http://www.aprelium.com
Back to top View user's profile Send private message Send e-mail
wonn1377
-


Joined: 20 Aug 2009
Posts: 1

PostPosted: Thu Aug 20, 2009 2:45 pm    Post subject: Migrate almost any data Reply with quote

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
Back to top View user's profile Send private message
DonQuichote
-


Joined: 24 Dec 2006
Posts: 68
Location: The Netherlands

PostPosted: Fri Aug 21, 2009 7:35 pm    Post subject: Two options Reply with quote

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.
Back to top View user's profile Send private message
jean3167
-


Joined: 31 Aug 2009
Posts: 1

PostPosted: Tue Sep 01, 2009 11:11 am    Post subject: Reply with quote

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.
Back to top View user's profile Send private message
radjiv
-


Joined: 13 Jul 2013
Posts: 1

PostPosted: Sat Jul 13, 2013 11:34 pm    Post subject: Reply with quote

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?
Back to top View user's profile Send private message
DonQuichote
-


Joined: 24 Dec 2006
Posts: 68
Location: The Netherlands

PostPosted: Tue Oct 29, 2013 2:51 pm    Post subject: MariaDB Reply with quote

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.
Back to top View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Aprelium Forum Index -> Databases All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB phpBB Group