Link to home
Start Free TrialLog in
Avatar of MariaHalt
MariaHaltFlag for United States of America

asked on

SQL Server Import and Export Wizard and tables with identity fields

When I export a table from our development instance of sql server to our production instance of sql server using SQL Server Import and Export Wizard, I find that if the table does not exist, it creates it but does not maintain the identity column.  It just becomes an int field with no identity attribute.  Is there a way to use the wizard without having to first script the table create, create it on the prod server and then add the data?  I tried checking off Enable Identity Insert but that made no difference.
Avatar of lcohan
lcohan
Flag of Canada image

What version/edition/service pack of SQL are you using? Are you positively sure the Enable Identity check box is checked and can you see that like in posting below? This should work unless you have some SQL bug in your environment - I just double checked it on SQL 2008 R2 and is all good.

http://blog.sqlauthority.com/2010/04/04/sql-server-enable-identity-insert-import-expert-wizard/
http://msdn.microsoft.com/en-us/library/ms189660(v=sql.100).aspx
ASKER CERTIFIED SOLUTION
Avatar of zaizai1780
zaizai1780

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MariaHalt

ASKER

Thanks, when I looked at the menu item closer, it says Export Data.  I'll just have to use SQL Compare to super double check that my identity field is an identity field.
Avatar of zaizai1780
zaizai1780

You are welcome! :)