CODEWITHSUNDEEP
sql-serverinsertauto-increment
user3178144
user3178144

Reputation: 23

How to insert existing data into auto-incrementing field

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?

Upvotes: 1

Views: 672

Answers (2)

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

Wrap the INSERT statements with the SET IDENTITY_INSERT command:

SET IDENTITY_INSERT [table_name] ON

...

SET IDENTITY_INSERT [table_name] OFF

Upvotes: 0

TomTom
TomTom

Reputation: 62093

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 }

Upvotes: 2

Related Questions