Adding PRIMARY KEY and IDENTITY attributes to SQL Server 2008 tables

Question

An application is using an SQL Server 2008 database that has ID columns, but these columns are not primary keys and have no IDENTITY attribute.

What I need to do now is adding the PKs and IDENTITY attributes. However, old IDs must be preserved.

Also, I need to do this with SQL commands (no GUI).

Problems

I tried all the following solutions, with no success.

• I can't directly INSERT/UPDATE an IDENTITY value
• I can't disable IDENTITY constraint for more than one table simultaneously
• I can't copy data with ALTER TABLE ... SWITCH TO (it doesn't work if origin table has not PK and target table has a PK)

• I can't use sequences instead of IDENTITYs for primary keys, because 2008 doesn't support sequences

• The following doesn't work either:

  SET IDENTITY_INSERT mydb.dbo.AZIENDE_NEW ON;
  
  INSERT INTO mydb.dbo.AZIENDE_NEW 
      SELECT * 
      FROM mydb.dbo.AZIENDE_OLD;
  

I get error

Msg 8101: An explicit value for the identity column in table 'mydb.dbo.AZIENDE_NEW' can only be specified when a column list is used and IDENTITY_INSERT is ON

This seems to me illogical. IDENTITY_INSERT should be ON for that table...

I'm open to other solutions, but I have no more ideas.

Answer 1

The easiest way to do this is to use the SSMS GUI, right click the designer and use "Generate Scripts". That generates you a table rebuild that preserves data and makes the schema changes for you.

If you care about performance you need to use the SWITCH technique. Make the schema of the two tables compatible with switch, then switch, then alter the schema back. That way you can add the IDENTITY property without (or with reduced) data movement.

SWITCH from a table with no PK to a table with a PK

Yes, that doesn't work. Create a compatible table for use with switch. You can switch heaps just fine, but both sides need to be heaps.

---

You can write the rebuild script yourself.

1. Create a table that has the desired schema
2. IDENTITY_INSERT ON on that table
3. INSERT...SELECT the data over to that table
4. DBCC CHECKIDENT(RESEED) the temp table
5. Delete the original table and rename the temp table to have the original name