SQL Server - auto-increment on a generated table

Question

Here is what I am 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 the existing one, and then enable auto-increment for the ID field. However, when I try to insert rows, I get the following:

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

Can someone enlighten me about what I am doing wrong or how should I actually do this?

Answer 1

The created table does not have an identifier. If you check the code you entered, you will notice that no identity is declared anywhere. I think you are confusing the concept of PRIMARY KEY and IDENTITY . They are different and not related to each other. You cannot create a table with an identity column using SELECT ... INTO ...

Answer 2

the column must be specified with the option "identity (1,1)". The first 1 is the starting point, and the second is the increment.

Answer 3

set identity_insert on does not actually do what you think. It disables auto-increment (temporarily) so that you can hardcode values ​​there.

Here is more information about this ... http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

Answer 4

First you need to create a new table without using "select ... in" and specify the column as the identity column.

Use "set identity_insert .. on" before copying data. Then "set identity_insert ... off" when you are done.

If you want to trick the first part, you can use the management studio. Right-click the source table ... go to the designer. Add an identification parameter for the desired column. Then save the script, but do not commit. Perhaps you want to create a new script file to create a new table. There are other more complex solutions if you have to do this repeatedly. But this is by far the simplest.

Answer 5

This may cause a problem:

 ALTER TABLE new_table ADD CONSTRAINT pk_new_table_id PRIMARY KEY(id, idColumn); 

Specify the id column.