Why insert new have must "SET IDENTITY_INSERT TableName ON"

Dong Lam Trien

I have a few for example insert new records, when the store must have "SET IDENTITY_INSERT TabeleName ON"; and "SET IDENTITY_INSERT TabeleName OFF"; other examples do not need 2 lines ON and OFF, if I wanted to give my 2 lines to adjust what ? you see the code below:

strSQL = "SET IDENTITY_INSERT TabUsername ON ";
strSQL = strSQL + "INSERT INTO TabUsername(USERNAME";
strSQL = ...
strSQL = strSQL + "SET IDENTITY_INSERT TabUsername OFF";
...
SqlCommand cmd = new SqlCommand(strSQL,cnn);
try
{
cmd.ExecuteNonQuery();
cnn.Close();
}
....

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi12y ago
hi,
Auto identity column is used to generate auto number in column.you cannot insert the value into this column.
SQL server provide the way to insert the value into this column using SET IDENTITY_INSERT TabUsername ON / OFF statement.
Please refer
http://technet.microsoft.com/en-us/library/bb734402(v=sql.110).aspx
hope this will help you.
Dong Lam Trien12y ago
I ran a few examples have must command lines ON and OFF new stored it in SQL Server but a few example other no need 2 lines ON OFF but still be stored in SQL Server, I guess on SQL Server to tune in item no need to use on line 2 (ON OFF), because the examples I ran along one machine, data file attch into SQL Server and i don't moving data from 1 server to another and you have to match ID values.
Ahmar Husain12y ago
Identity columns are the auto generated columns in the database which means you do not need to insert value into it ,however some times cases arise where you need to send your own value in the identity column in such case you SET IDENTITY_INSERT ON which allows table to receive a user value into the identity column. to bring back the table to its original state of auto insertion of Identity columns you need to SET IDENTITY_INSERT OFF.