MS SQL Server

How to Use SQL Server Identity Insert

An identity column in SQL Server refers to a column that auto-generates a unique numerical value for every record inserted. An identity column is very useful as a primary key as each value is unique for every row.

In this guide, we will understand how to use the identity property in the SQL Server and how we can manually insert values into an identity column.

SQL Server Identity

The identity property in a column is determined by the initial seed value and the increment integer. The syntax is as shown:

identity (seed, increment);

  1. The seed parameter defines the value of the first record inserted in the table.
  2. The increment determines by which value from the previous row is added.

If the seed and increment parameters are not defined, SQL Server defaults to values of 1, 1, respectively.

The example query statements illustrate how to use the SQL Server identity property:

createdatabasetemporary_db;
usetemporary_db;
createtablesample_table(
    idintnotnullidentity(1,1) primary key,
    namevarchar(50),
);
insertintosample_table(name)
values ('Abigail Henderson'),
       ('Peter Miller'),
       ('Harris Gonzales'),
       ('Anne Jenkins'),
       ('Katherine Patterson');
select * fromsample_table;

We should have a table as shown:

Notice the id column starts from a value of 1 and increments the next row by 1, as defined by the identity property.

SQL Server Indentity_Insert

Although the identity property is used to define an auto-generate feature, you may want to manually edit the value of an identity column.

This is where the identity_insert command comes into play.

SQL Server Enable Identity_Insert

By default, SQL Server will prevent you from manually editing the values of an identity column. To use it, you need to enable it on your target table.

For example, if you try to manually edit the values in the id column from the sample table above, you will get an error as shown:

insertintosample_table(id) values (7);

Use the following command syntax to turn the identity_insert feature on or off:

setidentity_inserttable_nameon/off;

For example, to turn identity_insert on for the sample_table created above, we can do:

setidentity_insertsample_tableon;

Once enabled, you can insert values into the identity column:

insertintosample_table(id) values (7);
[/c]c
The query should return success:
[cc lang="mysql" width="100%" height="100%" escaped="true" theme="blackboard" nowrap="0"]
(1 row affected)

HINT: You can only set identity_insert on a single table per session. If you try to enable identity insert on another table in the same session, the SQL Server will return an error as shown:

Conclusion

The SQL Server identity property allows defining a column that auto-generates numerical values based on the seed and increment values. However, as discussed in this tutorial, you can use the identity insert feature to manually edit the values of an identity column. We hope you found this article helpful. Check out other Linux Hint articles for more tips and information.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list