View previous topic :: View next topic |
Author |
Message |
phirez -
Joined: 22 Apr 2006 Posts: 57
|
Posted: Mon Jul 16, 2007 6:12 pm Post subject: Ms sql server migration to mysql. |
|
|
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 |
|
![](templates/subSilver/images/spacer.gif) |
aprelium -
Joined: 22 Mar 2002 Posts: 6800
|
Posted: Mon Jul 16, 2007 10:06 pm Post subject: Re: Ms sql server migration to mysql. |
|
|
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 |
|
![](templates/subSilver/images/spacer.gif) |
wonn1377 -
Joined: 20 Aug 2009 Posts: 1
|
Posted: Thu Aug 20, 2009 2:45 pm Post subject: Migrate almost any data |
|
|
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 |
|
![](templates/subSilver/images/spacer.gif) |
DonQuichote -
Joined: 24 Dec 2006 Posts: 68 Location: The Netherlands
|
Posted: Fri Aug 21, 2009 7:35 pm Post subject: Two options |
|
|
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 |
|
![](templates/subSilver/images/spacer.gif) |
jean3167 -
Joined: 31 Aug 2009 Posts: 1
|
Posted: Tue Sep 01, 2009 11:11 am Post subject: |
|
|
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 |
|
![](templates/subSilver/images/spacer.gif) |
radjiv -
Joined: 13 Jul 2013 Posts: 1
|
Posted: Sat Jul 13, 2013 11:34 pm Post subject: |
|
|
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 |
|
![](templates/subSilver/images/spacer.gif) |
DonQuichote -
Joined: 24 Dec 2006 Posts: 68 Location: The Netherlands
|
Posted: Tue Oct 29, 2013 2:51 pm Post subject: MariaDB |
|
|
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 |
|
![](templates/subSilver/images/spacer.gif) |
|
|
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
|
|