Given following SQL (MS SQL 2008):
SET IDENTITY_INSERT MyTable ON
GO
INSERT INTO MyTable
SELECT * FROM MyTable
WHERE Id = @SomeId
GO
SET IDENTITY_INSERT MyTable OFF
This results in:
An explicit value for the identity column in table 'MyTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Well, do I really need to define a column list? Is there no way to keep it generic? E.g. if a developer adds a column later on, this column won't make it into this routine, and it even will fail if null is not allowed...