IDENTITY_INSERT Scope level

Some time we need to insert in the identity field also but what will be the impact if we want to Allow the  identity insert, see the below example:

Create a Test Table :
————————————–

CREATE TABLE sample(
id int IDENTITY(1,1)
,name VARCHAR(100)
,address VARCHAR(100)
,phoneNo VARCHAR(15)
)

In One Session, I am going to Allow the Identity Insert
————————————————————————–

SET IDENTITY_INSERT dbo.sample ON;

INSERT INTO sample(id,name,address,phoneNo)
SELECT 10,'user-2','address-2','014002555-2'


SELECT * FROM sample

In another session i am going to insert normally without ID value:
——————————————————————-
INSERT INTO dbo.sample(name,address,phoneNo)
SELECT 'user','address','014002555'

SELECT * FROM sample

—————————-

So the Conclusion is IDENTITY_INSERT will be Enabled on Session Level only. You can insert with identity value from one session and another session will be working normally with auto identity value.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: