Identity Insert and SQL Server 2005 Import Export Wizard

re: Identity Insert and SQL Server 2005 Import Export Wizard

mroku — Fri, 30 Jun 2006 15:02:49 GMT

Hi!
I'm afraid your workaround doesn't work. When you try to copy data from multiple tables joined by foreign keys you will get missing key errors if you don't use "Optimize for many tables" option.

My impression over SSIS is very very poor. So far everything I try to do with it doesn't work at all or doesn't work as expected.

re: Identity Insert and SQL Server 2005 Import Export Wizard

da9l — Tue, 04 Jul 2006 18:36:05 GMT

I've encountered this problem as well and I'd really like to get it fixed.

To make microsoft fix this, please vote for feedback item 135905 at connect.microsoft.com under the SQL server feedback connection!

/Daniel

re: Identity Insert and SQL Server 2005 Import Export Wizard

zlusc — Thu, 03 Aug 2006 01:29:00 GMT

I don't think the "Optimize for many tables" causing that trouble. I tried many times even only import one table, the default setting still keep Enable identity insert unchecked. It is really annoying.

re: Identity Insert and SQL Server 2005 Import Export Wizard

e6matt — Thu, 25 Jan 2007 00:55:43 GMT

I am exporting from SQL 2005 to SQL 2000. I am only doing one table with "Optimize for many tables" unchecked. "Enable Identity Insert" is also checked for the single table I am copying. The preview correctly shows the Primary Key column jumping from 71 to 73 (I deleted a row). However, after running the export the SQL 2000 table has an entry for 71 and 72. It doesn't matter what is checked, this always happens. Is there another way around?

re: Identity Insert and SQL Server 2005 Import Export Wizard

bruce.parker — Thu, 09 Aug 2007 20:04:08 GMT

I needed to copy a complete database from 2005 to 2000

This is how I did it - Assuming the table(s) you need in 2000 are all created with indexes, constraints etc but contain no data.

Run the following on the 2000 database

exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

Then use the export wizard on 2005,

select all tables and views

Edit Mappings and check the Enable Identity Insert box

UN check the Optimise for many tables box

Deselect ALL the views so that it is only tables that are copied

Run the export ignoring the warning

After the export is complete

Run the following on the 2000 database

exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

exec sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

This has ceratinly worked on the table I was having trouble with - I still need to check the rest but it is looking promising

I hope this helps

SQL Server 2005: Import / Export reset identity keys… no workaround… 2005 sucks period, the end « Rip’s Domain

Tue, 02 Oct 2007 03:24:06 GMT

PingBack from http://rip747.wordpress.com/2007/10/01/sql-server-2005-import-export-reset-identity-keys-no-workaround-2005-sucks-period-the-end/

re: Identity Insert and SQL Server 2005 Import Export Wizard

fcsobel — Mon, 19 Nov 2007 05:50:02 GMT

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

identity insert

identity insert — Thu, 26 Jun 2008 21:10:26 GMT

PingBack from http://rocky.finestmatingstories.com/identityinsert.html

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | Patio Chairs

Wed, 03 Jun 2009 05:26:43 GMT

PingBack from http://patiochairsite.info/story.php?id=29980

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | fire pit

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | fire pit — Sun, 14 Jun 2009 06:22:23 GMT

PingBack from http://firepitidea.info/story.php?id=924

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | unemployment office

Tue, 16 Jun 2009 11:03:55 GMT

PingBack from http://unemploymentofficeresource.info/story.php?id=15616

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | fix my credit

Wed, 17 Jun 2009 05:29:33 GMT

PingBack from http://fixmycrediteasily.info/story.php?id=17580

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | patio umbrella

Thu, 18 Jun 2009 07:00:09 GMT

PingBack from http://patioumbrellasource.info/story.php?id=1755

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | garden decor

Fri, 19 Jun 2009 10:14:06 GMT

PingBack from http://gardendecordesign.info/story.php?id=5147

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | porch swing

Fri, 19 Jun 2009 10:30:20 GMT

PingBack from http://fancyporchswing.info/story.php?id=2248

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | patio set

Troubleshooting Microsoft SQL Server Identity Insert and SQL Server | patio set — Fri, 19 Jun 2009 13:15:57 GMT

PingBack from http://patiosetsite.info/story.php?id=95