Inserting values into an identity column with MSSQL

When you have defined an identity column, MSSQL will create a new value of: last identity + 1. The identity column is usually the primary key for that table and is often used as a foreign key. This works most of the times when you have orders, invoices...

The last identity + 1 is different of the max(column) + 1.

  1. If the largest value in the column is 10.
  2. You add a new entry, you will get 11.
  3. You delete entry 11, the largest value is back to 10.
  4. You add a new entry, you will get 12! and not 11. 11 was already used.

But how to make it work when you move data from a production SQL Server to another SQL Server? You will often need to create your own identity number to match the foreign keys needed.

Data used

  1. USE adventureworks;
  2. go
  3. SELECT DepartmentID, Name, GroupName, ModifiedDate
  4. FROM humanresources.department;
  5. go
DepartmentID Name                                               GroupName                                          ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
1            Engineering                                        Research and Development                           1998-06-01 00:00:00.000
2            Tool Design                                        Research and Development                           1998-06-01 00:00:00.000
3            Sales                                              Sales and Marketing                                1998-06-01 00:00:00.000
4            Marketing                                          Sales and Marketing                                1998-06-01 00:00:00.000
5            Purchasing                                         Inventory Management                               1998-06-01 00:00:00.000
6            Research and Development                           Research and Development                           1998-06-01 00:00:00.000
7            Production                                         Manufacturing                                      1998-06-01 00:00:00.000
8            Production Control                                 Manufacturing                                      1998-06-01 00:00:00.000
9            Human Resources                                    Executive General and Administration               1998-06-01 00:00:00.000
10           Finance                                            Executive General and Administration               1998-06-01 00:00:00.000
11           Information Services                               Executive General and Administration               1998-06-01 00:00:00.000
12           Document Control                                   Quality Assurance                                  1998-06-01 00:00:00.000
13           Quality Assurance                                  Quality Assurance                                  1998-06-01 00:00:00.000
14           Facilities and Maintenance                         Executive General and Administration               1998-06-01 00:00:00.000
15           Shipping and Receiving                             Inventory Management                               1998-06-01 00:00:00.000
16           Executive                                          Executive General and Administration               1998-06-01 00:00:00.000

(16 row(s) affected)
  • DepartmentID is defined as an identity column.

Inserting a identity value

Not working

  1. INSERT INTO humanresources.department(departmentid, name, groupname)
  2. VALUES(99,'ABC','XYZ');
  3. go
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Department' when IDENTITY_INSERT is set to OFF.

Works

  1. SET IDENTITY_INSERT humanresources.department ON;
  2. INSERT INTO humanresources.department(departmentid,name,groupname,modifieddate)
  3. VALUES(99,'ABC','XYZ','2007-12-31');
  4. go
  5. SET IDENTITY_INSERT humanresources.department OFF;
(1 row(s) affected)
  • set identity_insert humanresources.department on; is what allowed us to enter the identity value by hand.
  • Don't forget the line set identity_insert humanresources.department off; to reset to the normal processing.
  1. SELECT DepartmentID, Name, GroupName, ModifiedDate
  2. FROM humanresources.department;
  3. go
DepartmentID Name                                               GroupName                                          ModifiedDate
------------ -------------------------------------------------- -------------------------------------------------- -----------------------
1            Engineering                                        Research and Development                           1998-06-01 00:00:00.000
2            Tool Design                                        Research and Development                           1998-06-01 00:00:00.000
3            Sales                                              Sales and Marketing                                1998-06-01 00:00:00.000
4            Marketing                                          Sales and Marketing                                1998-06-01 00:00:00.000
5            Purchasing                                         Inventory Management                               1998-06-01 00:00:00.000
6            Research and Development                           Research and Development                           1998-06-01 00:00:00.000
7            Production                                         Manufacturing                                      1998-06-01 00:00:00.000
8            Production Control                                 Manufacturing                                      1998-06-01 00:00:00.000
9            Human Resources                                    Executive General and Administration               1998-06-01 00:00:00.000
10           Finance                                            Executive General and Administration               1998-06-01 00:00:00.000
11           Information Services                               Executive General and Administration               1998-06-01 00:00:00.000
12           Document Control                                   Quality Assurance                                  1998-06-01 00:00:00.000
13           Quality Assurance                                  Quality Assurance                                  1998-06-01 00:00:00.000
14           Facilities and Maintenance                         Executive General and Administration               1998-06-01 00:00:00.000
15           Shipping and Receiving                             Inventory Management                               1998-06-01 00:00:00.000
16           Executive                                          Executive General and Administration               1998-06-01 00:00:00.000
99           ABC                                                XYZ                                                2007-12-31 00:00:00.000

(17 row(s) affected)
  • set identity_insert tablename on can only be enabled on one table at a time.
  • Don't forget the line set identity_insert tablename off;.
  • You must specify a value for the identity column.
  • The user must be the object owner, either a sysadmin role or a dbo.
  • If you insert a value greater than the current identity seed, this will reset the value of the identity seed.