Identity columns automatically assign a value for each new row inserted. Most of the time, you wouldn’t want to force a value in, but occasionally you may have special circumstances. If you need to insert your own value into an identity column, all you have to do is execute this statement in your stored procedure:

SET IDENTITY_INSERT YourTable ON

Example:

VALUES

Note that you MUST turn off the IDENTITY_INSERT property at the end of your stored procedure. 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 you issue a  SET IDENTITY_INSERT ON statement for another table, you’ll get an error message.

When using this statement in a stored procedure, make sure you place it after the AS, like this:

Other things to note:

• 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.
• User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles.