CODEWITHSUNDEEP
sql-server
Karl
Karl

Reputation: 6165

SQL Server - Autoincrementing on a generated table

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?

Upvotes: 4

Views: 10860

Answers (5)

sam yi
sam yi

Reputation: 4934

First, you have to create the new table without the use of "select ... into" and specify the column as an identity column.

Use the "set identity_insert .. on" before you copy the data over. Then, "set identity_insert ... off" when you're done.

If you want to cheat on the first part, you can use the management studio. Right click original table... goto the designer. Add identity option for the desired column. Then save off the script but do not commit. Might want to manipulate the newly generate script to create the new table. There are other more sophisticated solutions if you're going to have to do this repeatedly. But this by far is the simplest.

Upvotes: 1

pistipanko
pistipanko

Reputation: 775

Here could be the problem:

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

Specify the id column.

Upvotes: 0

sam yi
sam yi

Reputation: 4934

set identity_insert on does not actually do what you think it does. It disables autoincrement (temporarily) so that you can hard code values in there.

Here's more info on that... http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294227

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 ...

Upvotes: 7

sam yi
sam yi

Reputation: 4934

the column needs to be specified with " identity(1,1)" option. First 1 is the starting point and second is the increment.

Upvotes: 2

Related Questions