How to insert existing data into auto-incrementing field

Question

I used the SMAA to upsize an Access 2010 database to SQL Server 2005.

During the process a number of records were not imported into SQL Server due to some corrupt or illegal data. I have since cleaned up the data that was not imported and saved it to a temporary table in the database. I now want to insert that data into the original table. However, one of the fields, called Task_ID, is an auto-incrementing field. When I run a standard insert query, the resulting data auto-incremented and does not use the imported Task_ID value. Is there a way to get this data into the field without it being changed?

TomTom · Accepted Answer · 2014-02-20 13:50:08Z

2

Enable insertion of existing data for the upload, then turn it off again.

http://technet.microsoft.com/en-us/library/aa259221(v=sql.80).aspx explains how:

Basically it is a SQL commmand. The syntax is:

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

answered Feb 20, 2014 at 13:50

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pang · Accepted Answer · 2014-09-25 06:48:41Z

0

Wrap the INSERT statements with the SET IDENTITY_INSERT command:

SET IDENTITY_INSERT [table_name] ON

...

SET IDENTITY_INSERT [table_name] OFF

edited Sep 25, 2014 at 6:48

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Feb 20, 2014 at 13:49

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

5 Comments

user3178144 Over a year ago

Thanks guys. I thought it would be something like the Identity_Insert command. Unfortunately, it does not appear to be that simple. I'm getting the error:

user3178144 Over a year ago

Try this again. Thanks guys. I thought it would be something like the Identity_Insert command. Unfortunately, it does not appear to be that simple. I'm getting the error: "Explicit value must be specified for identity column in table 'TaskList'. The Identity column in this table is 'Task', which is the PK and which is not in my INSERT or SELECT statement because it is not in the table to be imported from. Is there another way to get around the auto-increment issue without conflicting with the PK?

user3178144 Over a year ago

I'm trying to narrow this down. I've created a new table of the historical data that includes all columns including the PK. When I try to do an INSERT using the IDENTITY_INSERT parameter I get a violation of the PK. I've tried using WHERE NOT EXIST to only insert the data if the PK value does not exist but I'm not making any progress. Can anyone provide me with a starting point on this?

Mike Perrenoud Over a year ago

@user3178144: Is the PK an identity column? If so, is it included in the INSERT and SELECT? The bottom line is this, if you're going to INSERT into the identity column, then you need to SET IDENTITY_INSERT ON. If you're going to SET IDENTITY_INSERT ON, then you need to INSERT into the identity. If you want it to build its own identities, leave IDENTITY_INSERT alone and do not include the PK in the INSERT and SELECT.

Arvo Over a year ago

You have to use explicit column lists for insert/select operation. Then you need to be sure that your PK doesn't already exists in destination - or, if duplicates need to be allowed, you have to create just another PK. Then you need to remember, that only one table can have identity insert enabled at the same time.

Collectives™ on Stack Overflow

How to insert existing data into auto-incrementing field

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest