SQL SERVER – Enable Identity Insert – Import Expert Wizard

I recently got an email from an old friend who told me that when he tries to execute the SSIS package, it fails because of some identity error. After a few series of debugging and opening his package, we finally figured out that he has the following problem.

Let us see what kind of set-up he had on his package.

Source Table with Identity column
Destination Table with Identity column
Following checkbox was disabled in Import Expert Wizard (as per the image below)

To solve this issue, we enabled the checkbox as seen in the above image. This way, we were able to fix the problem.

The reason why he was facing this error is because his destination table had the IDENTITY property which will not allow any kind of insert from the user. This value is automatically generated by the system when new values are inserted in the table. However, when the user manually tries to insert a certain value in the table, it this property stops the action and shows an error.

Enabling the  property “Enable Identity Insert” by checking the checkbox allows the values to be inserted in the identity field. This way, the exact identity values are moved from source database to destination table.

Let me know if this blog post is easy to understand, so I can further elaborate the details as necessary.

Reference: Pinal Dave (http://blog.SQLAuthority.com),

About these ads

24 thoughts on “SQL SERVER – Enable Identity Insert – Import Expert Wizard

  1. Pinal can you include more business intelligence stuff in you blog. hope you will cover the next new orleans microsoft bi sessions in your blog.

  2. thanks,
    i have done this (batch copy table) with error at the end of it, but forgot what i could do to solve it, and found your blog, it really helps thanks!

  3. I am copying tables using the SQl Server 2008 import and export wizard. In the “select source tables and views” window I change the mapping. I tried both checking and unchecking the box “Enable identity insert” but in both cases columns that had the “Identity specification” = Yes in the source db are copied to the target db with the “Identity specification” = No.
    Any idea?

  4. Is there a way to insert rows into an existing table (with an identity field) and generate the next unique number for the identity table. i.e. last unique key in destination table = 501, data to import needs to continue 502, 503, etc.

    Is using a cursor the best way to achieve this?

  5. Hi,

    I have one query. Though it is not much technical but i want to this. I want this check box you are talking about (Enable Identity) to be checked by default. Is there any way to make this by default. Actually i do have identity in all of my tables. Every time i do transfer data, i have to check this. Is there any help?

    Regards,

  6. Pingback: SQL SERVER – Understanding Identity Beyond its Every Increasing Nature – Quiz – Puzzle – 3 of 31 « SQL Server Journey with SQL Authority

  7. I am having the same problem as German above. I check Enable identity Insert and run the import, then check the imported table and the Identity is set to no.

  8. Pinal my man

    Same issue as Nikunj, I have hundreds of table and can do an import, but it would take me days to set each identity individually!

    How can we do it for all tables??

    Your help would be appreciated. Loving your work.

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  10. Hello Pinal, Great site!!!

    I have the exact opposite problem, I am importing a table without a id field into a table with an id/index column. The columns in both tables are identical apart from the source table with no id field.
    I want the data from the source table to be appended in the destination table and the destination ID field must continue to be automatically generated. the source data must stay in the same order if possible.
    Can you shed some light here please?
    Johan

  11. Hello Pinal,
    Thank you for sharing your knowledge. But where are you? Please reply to German and JLW, I am having the same issue where after importing the tables, the identity column which was in the source is not in the destination. The column is there but the column is not showing as identity.
    Thanks.

  12. Very good info! I found this post when I was searching for a way to turn on identity insert for multiple tables _before_ running an SSIS-package. This sorted it for me!

    Many thanks!!!

    /Fredrik

  13. Are you still helping people solve problems with sql server 2012 (64bit)
    I’m trying to import using (SSMS) an excel sheet.xlsx (2010) that has way more then 255 columns.
    but it truncs all columns after 25x. Its as if it thinks it’s a 2003 excel. I do have the
    ace.oledb.12.0 provider installed.
    this also fails. A link is created but fails the test connection:

    EXEC sp_addlinkedserver
    @server = N’E2′,
    @srvproduct=N’ExcelData’,
    @provider=N’Microsoft.ACE.OLEDB.12.0′,
    @datasrc=N’\\192.168.2.6\Apres\sandboxcustomFormsData\CPKG.xlsx’,
    @provstr=N’EXCEL 8.0′ ;
    Go

    I also tried the select into openrowset in the design pane of SSMS…… fails also,
    I also tried Ole object with winBatch fails also…

    There has to be something simple that I’m missing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s