SQL Server – Insert records with value in identity column

Hi guys,

Today I will give you a simple tip, more than a tip it is an script ready to run. You will be able to test an insert setting a value for a identity column on SQL Server.

This is a small example about how to insert rows into a table setting a value for a identity column on SQL Server. Basically the script will do the following:

  • Create a temp table.
  • Insert a row into the temp table without a specific value for the identity column.
  • Enable the mode that we can give a value for the identity column.
  • Insert two rows setting a value for the identity column.
  • Disable the mode that we can give a value for the identity column.
  • Insert a new row into the temp table without the value for the identity column. (We will do it again just to check that SQL Server will set the right value for our identity column).
  • Drop the temp table.

This is the example:

This is what would happen if we try to set a value for a identity column without setting the IDENTITY_INSERT as ON.

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

Click on the following link to see more details about the identity_insert:
SET IDENTITY_INSERT

Thanks for reading.
Like and Share if you found it may be useful to someone you know!

Would you like to check the Portuguese version?
Clique on the Brazilian flag -> Brazil

Leave a Reply

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

 
Translate »