An application is using an SQL Server 2008 database that has ID columns, but these columns are not primary keys and have no
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).
I tried all the following solutions, with no success.
- I can't directly
- I can't disable
IDENTITYconstraint 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.