CODEWITHSUNDEEP
sql-server
okkko
okkko

Reputation: 1070

Insert specific ID in identity field

Is it possible to insert specific value in identity field in a SQL table?

I deleted some rows and imported them again, but I need to insert some exceptions with exact IDs.

Upvotes: 16

Views: 21201

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460028

You can turn on/off inserting identity values with SET IDENTITY_INSERT On/Off:

To enable your custom values:

SET IDENTITY_INSERT TableName ON

To enable auto-values:

SET IDENTITY_INSERT TableName OFF

Note that you have to list the (non-nullable) columns explicitly if you want to insert identity values, like here:

INSERT INTO TableName (ID, Text, OtherColumns...) Values (99, 'foo', ...)

You can't omit the column-list by using this syntax:

INSERT INTO TableName Values (99,'foo')

Upvotes: 28

Related Questions