SQL Server insert values in to identity field

0
23

Identity fields are auto incrementing in nature , but some time we need to push values to this field while situation demands. In such cases do enable IDENTITY_INSERT on the table name before insert

SET IDENTITY_INSERT <table name > < ON/OFF>

Eg. imaging table Customers have a an identity field CustId

INSERT Customers(CustId) VALUES (100)

you will be getting this error

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table ‘Customers’ when IDENTITY_INSERT is set to OFF.

to overcome this first enable IDENTITY_INSERT ON

SET IDENTITY_INSERT Customers ON

Now insert values

INSERT Customers(CustId) VALUES (100)

Now mame IDENTITY_INSERT Customers OFF

SET IDENTITY_INSERT Customers ON

SHARE
Previous articlecPanel for Android Phone
Next articleSamsung Galaxy ace GPRS is not working solution
abhilash, is a programmer by profession and a technology addict with a passion towards browsing , shopping and imaging . My views may be biased on reviews. all images and videos are copyrighted to respective owners.

LEAVE A REPLY

Please enter your comment!
Please enter your name here