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
anIDENTITY
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
IDENTITY
s 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.
Best Answer
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 theIDENTITY
property without (or with reduced) data movement.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.