Setting Identity Insert On/Off on Table – Sql Server

This command is from SQL Server. This command is designed to enable users to set their own value for identity column in case they want. This is very useful for us. We use it in MERGE and RDA if we insert the client to send to the server where we want to make sure to insert row on the server with the IDENTITY value generated by the client.

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.


SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }



The following example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

Tags: , ,

Comments & Responses

Leave a Reply

Your email address will not be published. Required fields are marked *


+ 3 = 4

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>