SQL Server Migration To Refactored MySQL w/ InnoDB Key & Constraint Issues

Question

I am redeveloping a web application and its infrastructure originally using SQL Server 2005, ASP.NET & Windows 2003 to a LAMMP (extra M for memcached of course) stack and since the schema is heavily refactored (with very good reason to do so) I must write a custom migration app.

The problem is the InnoDB primary + foreign key constraints are hindering my ability to insert the data into its MySQL/InnoDB tables. I have already tried using DISABLE KEYS & FOREIGN_KEY_CHECKS methods and temporarily removing the auto-increment on the primary with it either throwing an error when try to make one of these changes such as the DISABLE KEYS since it is unsupported in InnoDB or trying to remove the primary key assignment on a column in an empty table or the migration app throwing errors saying key already exists when entering a record when the table is empty. Is there anything else that can be done beyond this besides removing all keys first and putting them back afterwards (which I assume will give me hell too)?

Thanks!

Answer 1

Turns out this works:

ALTER TABLE `site_oltp`.`members` MODIFY COLUMN `id` INT(11) NOT NULL;
SET FOREIGN_KEY_CHECKS = 0, UNIQUE_CHECKS = 0;

insert records....

ALTER TABLE `site_oltp`.`members` MODIFY COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `site_oltp`.`members` AUTO_INCREMENT = (LAST_ID_GOES_HERE + 1);

SET FOREIGN_KEY_CHECKS = 1, UNIQUE_CHECKS = 1";

Answer 2

I prefer a third party applpications for migrating my data, i use data loader when i was migrating MS SQL to Foxpro it work great, and it can migrate almost any database.

Download Free : http://www.dbload.com