Export Data utility not working appropriately to export the tables containing identity column?
Data could be exported but the identity property is showing as disabled on the destination server?
The issue is seen even after enabling the Enable Identity Insert?
If you are experiencing above symptoms, I would like to inform that this is a by-design behavior.
- Exporting the data using Export Data utility:
- Select the Enable Identity Insert property and click OK.
Complete data will be exported fine but the problem is as explained in below screenshots:
- Source Server – Identity property is enabled.
- Destination Server – Identity value is disabled. However, the data is present in the table.
The reason for the difference:
When you create a table using export wizard it does not create the table with Identity attribute. Check the create table SQL statement under
Edit mappings --> Column mappings --> Edit SQL. This SQL does not contain the Identity in the table creation script.
Approaches to fix this problem:
Approach 1: Check the create table SQL statement under Edit mappings --> Column mappings --> Edit SQL. This SQL does not contain the Identity in the table creation script. You could add IDENTITY in the create table script as shown in figure below:
Approach 2: Generate the table script
a. Right click on database --> Tasks --> Generate Scripts --> Select specific database objects --
-> Select the tables you want to script out --> Check the properties in Advanced and enable/disable the property, if required.
b. Execute this script to create the objects on the destination server.
Approach 3: Use Import Data utility on Destination server to import the data. While selecting the options, please check the Enable Identity Insert under Edit Mappings option (refer below screenshot).
Validate the column properties to ensure Identity is enabled.
SET IDENTITY_INSERT - https://msdn.microsoft.com/en-us/library/ms188059.aspx
Keep Identity Values When Bulk Importing Data (SQL Server) - https://msdn.microsoft.com/en-us/library/ms186335.aspx
Please share your feedback, questions and/or suggestions.
Attinder Pal Singh | CSS – Microsoft
Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.