Identitiy Insert On SQL Server

You need to know about Primary Key and Foreign key before you can read this article. You can read the article “What is Primary Key and Foreign Key“.

In a column with identity column property set, the values in the column automatically increase.

For example, you created a unique column named ID in the table. You want this column’s value to increase automatically 1 value each time a record is added to the table. You can do this by defining the identity property on this column.

If the table has a column with identity property set, identity column is not specified in the INSERT statement when you want to insert a record into the table. The identity column’s value increases automatically.

You can define the identity property when creating a table or later. The identity property cannot be set for an existing column in the current table. We will do this in another way. I will address this method later in the article.

With the help of the following script, let’s set the identitiy property for the ID column in the following properties when creating the table.

  • auto increment value will start from 1
  • auto increment value will increase one by one.
If you change the create table script like below, auto increment value will start from 3 and will increase two by two.
Let’s add a record to this table with the help of the following script. As you can see in the script below, there are 3 columns in our table, but there are 2 columns in the insert statement.
You can see which values are assigned to the ID column by selecting from the table after insert operation.

As you can see, the value for ID was 1 for Ankara and 2 for Istanbul.

Sometimes we need to move our tables to another database. In such cases, many methods can be used.

For example, by taking the script of our table, let’s try to transfer the records to the table we just created. When transferring, we need to preserve the existing values in the Identitiy column.

If Identitiy Column does not exist, we can do this with the help of the following script.

But since myIdentityYeni has a column with identity property, it will return with the following error.

Msg 544, Level 16, State 1, Line 7

Cannot insert explicit value for identity column in table ‘myIdentityYeni’ when IDENTITY_INSERT is set to OFF.

In order to do what we need, we need to change the query as follows.

After running the query as above, our insert operation will be completed successfully.

After creating a table, the identity property cannot be set for the current column. If you have such a need, you can recreate the table with the identity property and transfer the records from the current table to the new table by the above method.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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