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:

CREATE TABLE T1 ( PKint IDENTITY PRIMARY KEY, SomeTextvarchar(100))

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!