SQL Server - Autoincrementing on a generated table

Question

Here's what I'm trying to do:

SELECT * INTO new_table FROM old_table WHERE 1=2;
ALTER TABLE new_table ADD CONSTRAINT pk_new_table_id PRIMARY KEY(id);
SET IDENTITY_INSERT new_table ON

Basically, I want to create a new table based on the structure of an existing one, then turn on autoincrementing for the ID field. However, when I try to insert rows I get this:

Table 'new_table ' does not have the identity property. Cannot perform SET operation.

Can anyone enlighten me as to what I'm doing wrong or how I should actually be going about this?

Answer 1

The table you create doesn't have an identity column. If you inspect the code you posted, you will notice that nowhere is an identity declared. I think you are confusing the concept of PRIMARY KEY and IDENTITY. They are distinct and unrelated. You cannot create a table with identity column using SELECT ... INTO ...