{"id":577,"date":"2010-03-13T10:36:47","date_gmt":"2010-03-13T16:36:47","guid":{"rendered":"http:\/\/www.mooreds.com\/wordpress\/archives\/000577"},"modified":"2010-03-13T16:50:47","modified_gmt":"2010-03-13T22:50:47","slug":"moving-data-from-ms-sql-to-mysql","status":"publish","type":"post","link":"https:\/\/www.mooreds.com\/wordpress\/archives\/577","title":{"rendered":"Moving data from MS-SQL to mysql"},"content":{"rendered":"<p>I recently worked on a project where I needed to port data from a MS-SQL database to a mysql database.\u00a0 There are programs, both <a href=\"http:\/\/www.convert-in.com\/mss2sql.htm\">payware<\/a> and <a href=\"http:\/\/dev.mysql.com\/doc\/migration-toolkit\/en\/index.html\">freeware<\/a> that will help with this process, but I didn&#8217;t have ODBC access to the MS-SQL database, which these programs require.\u00a0 All I had were a bunch of insert statements that looked like this:<\/p>\n<p><code \/><\/p>\n<pre>INSERT INTO\r\n[sample].[dbo].[users_info]([ID],[registration_day],[registration_month],<\/pre>\n<pre>[registration_year],[registration_time],[first_name],[last_name],<\/pre>\n<pre>[username],[userpwd],[repeat_pwd],[birth_date],[birth_day],[birth_year],<\/pre>\n<pre>[street],[state],[city],[zip_code],[email_address],[membership_startdate],<\/pre>\n<pre>[online],[gender]) VALUES(463,N'15',N'7',N'2009',N'9:13:52 AM',N'Homer',<\/pre>\n<pre>N'Simpson',N'homerrocks',N'beerbeer',N'beerbeer',N'January',N'1',N'1999',<\/pre>\n<pre>N'234 Main',N'Alaska',N'Springfield',NULL,N'homer@thesimpsons.com'),<\/pre>\n<pre>CAST(0x9AE90000 AS SmallDateTime),<\/pre>\n<pre>CAST(0x00009CD700000000 AS DateTime),N'Man');<\/pre>\n<p>I wrote a perl script to turn that dialect of SQL into a mysql friendly dialect (feel free to <a href=\"\/files\/mssqltomysql.txt\">download it<\/a>).<\/p>\n<p>The most interesting parts were those <code>CAST<\/code> statements.\u00a0 <a href=\"http:\/\/www.orafaq.com\/forum\/t\/121996\/0\/\">This forum post<\/a> and <a href=\"http:\/\/www.dba-sql-server.com\/sql_server_tips\/t_super_sql_382_smalldatetime.htm\">this blog post<\/a> helped me turn those casts into real dates.\u00a0 (After loading the inserts into mysql, I did some post processing, using the <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/control-flow-functions.html#operator_case\">helpful case statement<\/a> and <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/date-and-time-functions.html#function_str-to-date\">str_to_date function<\/a> to rationalize some of the data.)<\/p>\n<p>[tags]mssql, mysql, data migration[\/tags]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I recently worked on a project where I needed to port data from a MS-SQL database to a mysql database.\u00a0 There are programs, both payware and freeware that will help [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,28],"tags":[],"class_list":["post-577","post","type-post","status-publish","format-standard","hentry","category-databases","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/comments?post=577"}],"version-history":[{"count":0,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/577\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/media?parent=577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/categories?post=577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/tags?post=577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}