CODEWITHSUNDEEP
sqlsql-server
Hypnoz
Hypnoz

Reputation: 1136

How to insert into table with specific primary key identity (1,1)

Like a table, we always prefer to make identity as primary key like identity(1,1) That way, that column will start 1 increment 1 when adding new row.

So could I ask whether I can add one row with specified number manually, like I can add one row with primary key 100

Upvotes: 6

Views: 15384

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Yes you can. Using SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }:

SET IDENTITY_INSERT YourTable ON

INSERT YourTable(Id, OtherField)
VALUES (100, 'Other Value')

SET IDENTITY_INSERT YourTable OFF

Upvotes: 7

podiluska
podiluska

Reputation: 51514

set Identity_Insert yourtable on

Then do the insert

insert yourtable (id, field) values(100,'hello')

Then turn it off again

set Identity_Insert yourtable off

Upvotes: 14

Related Questions