SQL Server 2005: Import / Export reset identity keys… no workaround… 2005 sucks period, the end
October 1, 2007
I’m getting a lot of people saying that they are disappointed that this problem isn’t fixed. Although I feel for you all, it doesn’t do any good venting your frustrations here. You need to be telling this to your sales representatives for Microsoft or where ever you bought SQL2K5.
Another thing you can do (if you can do it and your infrastructure allows it) is to down grade to SQL2K and then demand a refund from Microsoft sighting that their product doesn’t live up to your expectation.
This is the only way that Microsoft will listen.
UPDATE: You gotta love Microsoft’s cop out answer for why this doesn’t work and for leaving this bug open. There is no word yet on whether this will ever be fixed.
UPDATE: Seems I’m not the only one who’s pissed off by this.
UPDATE: Digg this thing up!
UPDATE: Reddit me!
I have officially labeled SQL Server 2005 worthless beyond belief. I really have no idea why something as basic as the import / export wizard (IEW) inside 2005 is broken beyond belief. Let me explain.
I have a current database that is a 2000 database with a bunch of tables that use an identity field as the primary key. What I’m trying to do is export the data that is in those tables to the 2005 database using the IEW. I’ve must of done this a thousand times in my career and have never had a problem doing it before. Normally I just run the I generate the scripts first (see my other blog post about the workaround for this with 2005) and then after creating the database on the new server, I just export the data from the old to the new. Easy, quick, never fails.
Now with 2005 there is a BIG bug that when you go to export the data from the old database to the new one; all of your identity values are reset. To put into perspective, let’s say you have a table that has 20 rows and the identity values range from 1-30 because of deletions that have happened on the table. When you go to export this table to the new table in 2005, you identity values are now 1-20 corresponding to the number of rows that are in the tables.
As you can see this is a big problem because it completely corrupts your data and renders it useless.
I’ve tried some workarounds that people have suggested to no avail. I really can’t believe that a bug like this has gone unnoticed and still isn’t even addressed within SP2 for 2005. Seems that Microsoft really screwed the pooch on this one and it make 2005 a piece of shit. Futhermore there isn’t an offer on my hosting plan to downgrade SQL Server to 2000; so I’m screwed.
I have found though that this isn’t the case if you use GUID for your primary keys, which I do now but in this database I use identity columns since I didn’t know any better at the times.
If anyone out there knows a workaround or a patch for this please speak up. Also voice your concerns to Microsoft about this since this is a bug that should of never existed in the first place. Until this problem is fixed, I’m officially calling 2005 shit and I’m recommending to all my clients to not upgrade.
October 2, 2007 at 6:37 am
Why don’t you generate the script for DB from 2k and execute it in 2005. I think this work for structure but data cannot be moved.
October 2, 2007 at 8:31 am
@Bob,
Yes, I can do that. Really the point of this post is to point out the failure of Microsoft to fix bugs in a very useful part of SQL Server. There is no reason why something like this shouldn’t have been addressed ages ago or even why the product was shipped with this not working.
Last night I even found out a way around the problem I’m having by just doing a standard backup and restore. This work around however, still doesn’t help the fact that the import / export wizard is broken and hasn’t been fixed. I mean for God’s sake, 2005 is on SP2 already!
October 2, 2007 at 12:05 pm
I have found that making sure “Optimize for many tables” remains unchecked is the solution.
Of course you have to click on each table, wait for the screen to come up, then select Enable Identity Insert.
The secret to not getting upset about bugs like this is remembering the many other ‘Sub-Optimal’ applications put out by Microsoft Corp.
October 2, 2007 at 1:14 pm
@dan
actually that isn’t solution or a workaround as per the link in the post:
http://blogs.msdn.com/chrissk/archive/2006/06/24/645968.aspx
This is an ongoing issue that Microsoft will not address, which is what is ticking me and many others off.
October 2, 2007 at 2:54 pm
I ran into this on the developer.com newsletter today. I don’t know if this will be of any help (Just skimmed it) but it might. Who knows with computers. http://www.developer.com/net/asp/article.php/3702826
October 10, 2007 at 3:46 pm
We ended up doing this a couple ways to see which worked better for us etc.
The first we ran a full backup and then restore to a fresh 2k5 database without issues. It was a bit time consuming, but in the end the identities and data was on point.
The second we simply took the db offline in 2k0 and then copied the mdb and ldb over to the 2k5 machine. We then attached it, and put it over into 2k5 compatibility mode. It ran a couple tools on itself and sent it on its way. I wasn’t a big fan of this one as I’m sure it will lead to ‘weird’ things down the road, but I dunno. It seemed to work ok.
The final way was to use replication. We had setup a remote replication between the two boxes and had the 2k0 machine be the witness/publisher etc. We ended up having to put a call into MS , but in the end the data replicated fine over to the new 2k5 db’s without problem.
November 18, 2007 at 10:47 pm
After spending a day fighting with this I learned about SqlBulkCopy from a post on David Hayden’s blog and created this quick app to get around the problem.
It can copy data and keep identity and nulls intact. Used with the generate script wizard you can make a complete database copy as long as you have sql access.
It’s like having the SQL 2000 wizard back
You can get it here:
http://projects.c3o.com/files/3/plugins/entry11.aspx
November 24, 2007 at 9:45 pm
Made some much needed enhancmenets to the Simple SQL Bulk copy app. Changed checkbox list to DataGrid to display errors. Changed call to asynchronous for better feedback. Added user settings to remember source and destination.
You can get it here:
http://projects.c3o.com/files/3/plugins/entry11.aspx
January 7, 2008 at 9:18 am
At last someone telling it how it is! This bug has caused me so many problems. It is a major bug and renders SSIS almost useless.
January 12, 2008 at 5:07 pm
Fred , Thank you so much, your tool saved me a big time. Shame on MS for not responding this issue in a right manner for long time
January 23, 2008 at 9:40 am
Thanks guys, you confirmed what I had experienced. I just assumed that I was missing something and did not have time to dig deeper into SQL2k5 land to figure how to do import / exports correctly. Though I had already spend hours and hours trying to get the import / export to work. This issue caused us to lose lots of development time/$. SQL2k5 came with a new VPS that we migrated to. I can say with out reservation, moving to SQL2k5 was the worse IT choice we every made. I have yet to see any major advantage or new feature that we used in our web development over the last year.
January 23, 2008 at 10:31 am
@Mark,
You might want to contact your VPS company and demand that they switch you to a VPS with SQL2K installed on it. Tell them the reason and have them complain to Microsoft about the problem.
January 30, 2008 at 1:11 am
As above, I encountered this while trying to merge two databases. “Am i insane”? I thought. Surely it can’t be a bug?
WTF are they thinking leaving stuff like this unfixed?
January 30, 2008 at 10:07 am
@Peter,
I feel for you my brother… I feel for you.
February 4, 2008 at 3:35 am
I read the blowing informative thread. can anyone let me know why am i not able to change the identity column to yes. when i import it. its tiring.
thanks
dan
February 14, 2008 at 11:50 am
Hey Fred,
I have tried to download and run from the site but unsuccessfully. Really need your tool to work around SQL 2005, can you repost it on the web. That would be very appreciated.
CPP
February 20, 2008 at 6:12 pm
Overall the 2005 Import / Export wizard is a MAJOR step backwards.
Field types and length no longer default to the correct values when importing or exporting, forcing you to redefine each and every field. Exporting varchar field to Access will default to LongText. This results in all the field in your access database being set to Memo.
February 20, 2008 at 8:02 pm
Hey everyone, don’t complain here, complain to Microsoft. They need to be told that 2005 is a piece of shit.
March 19, 2008 at 2:23 pm
If you wish to preserve identity column values when importing data to SQL Server 2005 then try the following:
When you use the SQL 2005 Import and Export Wizard and you reach the screen titled “Select Source Tables and Views” click the button at the bottom labeled “Edit Mappings”. Check the checkbox labeled “Enable Identity Insert”. This seems to work for me.
March 19, 2008 at 5:00 pm
Aleric,
Edit mappings>Enable Identity Insert worked for me as well.
April 4, 2008 at 12:37 pm
I’m using a SQL 2005 database now and importing data using the wizard from one db to another and need the PKs (Identity columns) to be the same in both. I hope the Enable Identity Insert option will work. I found that you don’t have to uncheck optimize for many tables. You can highlight all your tables and edit settings for all of them at once (including Enable Identity Insert). If this works, then it seems that all the complaints above about a bug in SQL 2005 are bogus??? I hope this works, if not I hope the tool posted above works and is available. I’ll report my results.
Roger
April 29, 2008 at 6:06 pm
I was experiencing a similar problem - the identity not inserting, the delete existing records not working. I edited the mappings and removed the optimize check but I still seemed to have the problem. I was not able to use the BulkCopy because I’m trying to import to a hosted website. My solution was to keep the import to no more than 20 tables at a time. It seems to automatically do the optimize if you have more than 20 tables - even if you removed the checkbox. You get a visual clue to this when you look at the details on the final screen of the wizard - it gives you each table listed separately and how many rows it inserted. If you aren’t getting this then I think it is still optimizing the import and you’ll probably get errors/bad data.