Inserting specific values into identity columns

If you inherit a database that was created by another developer, it may not be designed to your standards. Arthur Fuller demonstrates how you can tackle a specific problem that you might encounter in these databases: how to rebuild a table (T1), while preserving the primary key (PK) values in T1.

Here is the problem du jour: You must rebuild a table (T1), and make sure you preserve the primary key (PK) values in T1. You want to move all the data into table T2, which has an identity key, while preserving the PKs from table T1. Once you move all the T1 data into T2, you want T2's identity key to work as expected. That is, suppose the highest key you insert is 12345, then once you're finished, the next insert should be keyed 12346.

Just to add a wrinkle to the problem—one borrowed from an app I am currently working on—we not only have two tables but we also have two separate applications: one designed for accounting purposes and the other designed for much more detailed management of the projects involved.

The accounting database was built after the fact. The ProjectIDs in this table are integers but not identity keys. In the prior application, ProjectID is an identity key. Fortunately, the developer defined the PK as Identity( 10000, 1 )—after examining the keys in the accounting app and determining that this seed was guaranteed to create no conflicts.

How do you force T1's data into T2?

Assuming that all your other ducks are in a row (column types and counts match), now you have to figure out how to force T1's data into T2. The code in Listing A creates a test table with an identity key, inserts a couple of rows while allowing SQL to determine the identity key, forces a new row in with a specific key, and finally inserts a fourth row allowing SQL to generate its key. (Before running this code, choose a test database.)

So far, so good. We have demonstrated how to forcibly insert a given PK into a table. You typically need to do this when importing identity keys from one table into another. However, there is a problem. Run the code in Listing B.

It turns out that this bizarre behavior was allowed due to the sloppy coding in the table-creation statement. We should have written the following:


This code changes the behavior to what you would naturally expect. When you run the line that attempts to insert a duplicate PK, you get the following error message:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__T1__7F60ED59'. Cannot insert duplicate
 key in object 'T1'.
The statement has been terminated.

Summing up what you've learned

While this problem isn't common in an intelligently designed database, it does arise in the other 90 percent of databases. Therefore, remember the following lessons:

  • You can force specific entries into any identity key column.
  • You must ensure that said column has explicitly been defined as a PK column; otherwise, you're inviting disaster.
  • After forcing the explicit entries you need, make sure that you SET Identity_Insert OFF.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks