I'm in the process of rewriting some terrible software we've been using at work for, oh, about five years. It keeps track of customers in a loyalty program, handles adding/updating contact information, exciting stuff like that. It's pretty much done (in php/MySQL), but some of the data needs to be copied on a regular basis (doesn't need to be realtime) to the local server (not accessible from the internet, just from the LAN) which stores this information for use at the POS (in MS SQL).

Here's how the existing system does this: somebody has been manually copying/pasting this into the local database. For five years. Seriously. Obviously, this is riddled with problems--data is copied incorrectly, data isn't copied often enough, data is missing, etc.

I have two needs: 1) to get a complete copy of the MySQL data into MS SQL as soon as possible, hopefully via some non-painful means (painful meaning ctrl-c, ctrl-v, repeat 15,000 times). I know less than nothing about MS SQL. I tried saving the appropriate fields as a tab delimited text file and importing that, which worked, but every field was imported as varchar(8000). I changed the columns to the appropriate data types (mostly varchars and datetimes), but our POS system didn't like that (in that it couldn't find anybody in the system anymore).

2) I need to come up with a (hopefully) non-kludgy means of automating this process for the future. Ideally something that would be automated, or, barring that, require only one or two steps on my part each time.

Any recommendations or ideas?

tl;dr I need to copy data from MySQL to MS SQL halp

6/13/2010 4:40:12 PM

How many tables/fields are we talking about here? My first thought would be just to write some quick code to read in the MY SQL data row by row and insert it into the MS SQL database

6/13/2010 4:48:47 PM

It's not a huge amount, about 3000 rows with 8 fields each.

6/13/2010 4:49:50 PM

There are a bunch of ways you could do this...what languages do you actually know? If you can deal with MySQL then you can probably work with MS SQL enough to accomplish this. The two SQL languages are mostly the same for basic functionality.

6/13/2010 4:58:53 PM

I'm comfortable working with php, C#, and Java, I guess. I've only done database stuff in php, but I'm not opposed to learning something new.

6/13/2010 5:04:59 PM

mySQLyog could do this right? It can export one table as CSV/Excel/text/whatever you want pretty much.

You could just figure out how to import into MS SQL from a external file and your gg.

[Edited on June 13, 2010 at 6:00 PM. Reason : s]

6/13/2010 5:59:36 PM

SSIS

[Edited on June 13, 2010 at 7:47 PM. Reason : http://thewolfweb.com/message_topic.aspx?topic=592083]

6/13/2010 7:43:58 PM

^ and the odbc driver for mysql.

6/13/2010 8:07:35 PM

SSIS is pretty nifty

although, despite being a recent .NET convert, i still despise everything that is MSSQL. :shudder:

6/14/2010 2:40:52 AM

I don't know why I was trying to do this with a file downloaded from our web server. I ended up downloading the MySQL drivers for .net and made a program to get the data from our hosted database and insert it into our local database. Took all of 10 minutes to write and I just have to run it in the future any time I need to refresh the data. Thanks for the suggestions!

6/20/2010 11:23:31 AM

Also, here's a tidbit from the code I'm replacing:

	if(!request.getParameter("home_phone").equals(""))
			strHome_phone=request.getParameter("home_phone");

		if(!request.getParameter("home_phone1").equals(""))
			strHome_phone+=request.getParameter("home_phone1");

		if(!request.getParameter("home_phone2").equals(""))
			strHome_phone+=request.getParameter("home_phone2");
	
	if(!request.getParameter("mobile_phone").equals(""))
			strMob_phone=request.getParameter("mobile_phone");

		if(!request.getParameter("mobile_phone1").equals(""))
			strMob_phone+=request.getParameter("mobile_phone1");

		if(!request.getParameter("mobile_phone2").equals(""))
			strMob_phone+=request.getParameter("mobile_phone2");

	if(!request.getParameter("other_phone").equals(""))
			strOth_phone=request.getParameter("other_phone");

		if(!request.getParameter("other_phone1").equals(""))
			strOth_phone+=request.getParameter("other_phone1");

		if(!request.getParameter("other_phone2").equals(""))
			strOth_phone+=request.getParameter("other_phone2");

6/20/2010 1:59:29 PM

i like how it's appending one after another into a string too, rather than separate strings

6/20/2010 4:51:17 PM

i despise people who use underscores in their variable names

also, what happens if, say, home_phone and home_phone1 both have values? won't you end up with something like "(919) 555-1212(919) 666-1313"?

6/20/2010 5:49:44 PM

i don't know, i've started using underscores more often now instead of camelCase

just seems cleaner to me for some reason

6/20/2010 5:53:40 PM

nothing like making sure you don't append empty strings to something

6/20/2010 7:30:27 PM

haha yeah

6/20/2010 8:36:02 PM

lol

6/20/2010 8:41:03 PM