CODEWITHSUNDEEP
sql-serverdata-importsql-import-wizard
r3plica
r3plica

Reputation: 13367

SQL Identity Insert is not creating Identity columns

I am importing data into a blank database and for some reason it is not creating identity columns.

I have selected "Enable identity insert"

enter image description here

But it is not creating the identity column. This is the original one:

enter image description here

id is an identity column, but in the imported table:

![enter image description here

Which hasn't created an identity column. Does anyone know why?

Upvotes: 0

Views: 2916

Answers (3)

Nits Patel
Nits Patel

Reputation: 390

may it help

create table YourTableName
(
ID int identity(1,1),
contact_ID int,
ipaddress varchar (50),
task varchar (50)
)

And if you want to insert value in identity column you need to write something like this EXAMPLE :

SET IDENTITY_INSERT YourTableName ON

INSERT into YourTableName(TheIdentity, TheValue)
VALUES (1, 'First Row')

SET IDENTITY_INSERT YourTableName OFF

as you say you have 300+ tables

Go to your column property :

In identity Specification section change IsIdentity No to Yes (By default identity Specification will be NO change to Yes) Identity Increment 1

Upvotes: 0

r3plica
r3plica

Reputation: 13367

I figured this out. It right clicked the original database and clicked "Tasks > Generate Scripts" to a new query window. Then I used the Object Explorer (F7) on the target database and deleted all the tables. I made sure the scripts I created in the last step were targeting the new database and then I ran them.

Once this was done, I was able to use the import wizard and check the "Enable identity insert".

This worked.

Upvotes: 1

Neil Norris
Neil Norris

Reputation: 411

simple. Right click the table, and go to design in SSMS, highlight the primary key.

Check that Identity Specification has the following values.

  • Identity Specification = Yes
  • (Is Identity) = Yes
  • Identity Increment = 1
  • Identity Seed = 1

Upvotes: 1

Related Questions