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

Question

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

Answer 1

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

Answer 2

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