Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

transfer data from mysql to mssql2000

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...
0
Dalexan
Asked:
Dalexan
  • 5
  • 3
1 Solution
 
Ephraim WangoyaCommented:

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
0
 
DalexanAuthor 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.
0
 
8080_DiverCommented:
If both of the databases are "active", I would NOT recommend writing the data, including the Identity columns, from mySQL to SQL Server . . . you are liable to cause duplications in what should be an Identity column.

Do some of the tables have other tables' Identity column referenced as FK's?  If so, this is going to be a bit tricky.  There are a couple of approaches to doing this, though.

One approach involves adding (at least temporarily) two INT columns (mySQLID and NewID) to the SQL Server tables that will be receiving the data.  Then you move the mySQL Identity value into the new, (mySQLID  column.  Once you have transferred all of the tables (moving the IDent column as needed to the (mySQLID column), then you can create an app that is much simpler and that sets the NewID column to the value of the SQL Server Identity column that f the row that has the mySQL Ident.  (Note, the reason for doing this in a second column is so that you can run and rerun the remapping process until you get it working right and you will have only had to import the data once. ;-)  Once you have all of the remapping identified, you can create another fairly simple app that updates the FK's to the NewID's based upon a match to the mySQLID's.

It is a little labor intensive but not nearly as bad as coding all of the columns and trying to figure out how to remap the FK's in any other manner. ;-)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
8080_DiverCommented:
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. ;-)
0
 
8080_DiverCommented:
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.
0
 
DalexanAuthor 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 :-)
0
 
8080_DiverCommented:
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 ;-).
0
 
DalexanAuthor 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.
0
 
8080_DiverCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now