Export Data Utility Problem With Identity Column

  • Article

Problem Statement

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.

Repro Steps:

  • Exporting the data using Export Data utility:

1

 

  • Select the Enable Identity Insert property and click OK.

exportutility_2

Complete data will be exported fine but the problem is as explained in below screenshots:

 

  • Source Server – Identity property is enabled.

              sqlserverexport_identity3

 

  • Destination Server – Identity value is disabled. However, the data is present in the table.

               export_4

 

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.

                     identity_5

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:                       6

 

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.

             Refer article - https://technet.microsoft.com/en-us/library/ms178078(v=sql.105).aspx

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).

7

Validate the column properties to ensure Identity is enabled.

 

Related Articles:

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.

Thanks,

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.

Comments

  • Anonymous
    December 21, 2016
    It is a very nice article. Thanks!
  • Anonymous
    December 22, 2016
    Helpful article sir g