We help IT Professionals succeed at work.

transfer data from mysql to mssql2000

Medium Priority
407 Views
Last Modified: 2012-08-14
I need to transfer data from several tables in mysql to identical tables in mssql.  The only catch being, some of these tables have auto-increment keys with some records pointing to others, and both systems are active.  I've considered using vb to do this but would need, I think, stored procedures to do the inserts so I can receive the keys as return values to be put into the other records.  It seems to me to be a lot of code to retrieve all the fields from one, assign them to parameters for the sp, and writing the sp's to insert these records and return the assigned key.  With 10 tables and close to 200 fields, this is a LOT of coding.  This is all something that will need to be run every 5 minutes for the next 4-6 months until the conversion is complete and we can shut down the old system.  Does anyone know of a simpler way to do this?  Please help...
Comment
Watch Question

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

Initially you can have the fields in SQL Server just as primary keys and not Identity columns

When you are done with the conversion, change the fields to identity and reset the start values

Author

Commented:
Actually, we are going the other way...our new system is in mysql.  Currently, both systems are live and the apps are creating records, including the interrelating keys.  Our reporting is still tied to the old system and our clients insist upon 'one' report to cover both...some of the reporting includes the sequence keys and they need the keys to be contiguous...thus the idea to copy transactions from the new to the old so reporting on the old system can send out everything at one time.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Okay, if you're going from SQL Server to mySQL (a decision I will not comment on ;-), then you just need to have the 2 columns added to the mySQL database and work everything in the other direction. ;-)
Of course, you will probably want to test this on a dev database first and then schedule the whole thing as during a weekend maintenance period.

Author

Commented:
8080 (really?  so few of us left...), Perfect!  That should make the copy easy, and the mods needed to 'fix' the references before reporting is run rather simple.  Thank You :-)
Dalexan,

Actually, that is a first regarding my handle. :-D

Yes, I do date back to the 8080 days (and, actually, about 10 or more years past that); however, the handle reference is to SCUBA diving.  An 80/80 diver is one who prefers 80F and 80ft visibility . . . as in, Grand Cayman, Jamaica, and (the one I hope to someday dive) the Adriatic.

Good catch, though, on the Intel 8080.  I had a Xerox Z80 (based on the 8080H chip) that ran at 10MHz . . . which pretty much ran circles around the IBM PC when it first came out (at 1.77MHz ;-).

Author

Commented:
Mine was a Heath-89 with the Z80...and a Rockwell AIM 65 with the 6502 prior to that.
Interesting on the diving...good luck getting to the Adriatic.
It's a dream . . . that water is sooooo clear!  I got there once before (before I was diving) and, standing in chest deep water, I could see the cuticles on my toes! :-O :-D
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.