sql server -- identity problem

[grzes]

MS SQL Server 2000.
My case is: I have the table T with primary key calling __recid int
without identity property. This table includes a lot of records (about
1000000). I need to convert __recid's data type to IDENTITY.

As you know sql sentence: "alter table T alter column __recid int
IDENTITY not null" does not work with not-empty tables.
I use the SQL Enterprise Manager which can convert the field __recid
into identity but I need another way to solve the problem, probably I
should use TSQL. I don't know...

So I need your help.

regards
grzes

[Lyle H. Gray]

gj*@o2.pl (grzes) wrote in news:e02bcd3.0411170538.5ab97764
@posting.google.com:

MS SQL Server 2000.
My case is: I have the table T with primary key calling __recid int
without identity property. This table includes a lot of records (about
1000000). I need to convert __recid's data type to IDENTITY.

As you know sql sentence: "alter table T alter column __recid int
IDENTITY not null" does not work with not-empty tables.
I use the SQL Enterprise Manager which can convert the field __recid
into identity but I need another way to solve the problem, probably I
should use TSQL. I don't know...

Easiest method is to create a new table with an IDENTITY column and copy
the rows from the old table into it. Then rename the tables appropriately
so that the new table has the old table's name.

Regards,
Lyle

[David Rawheiser]

Please note that you will have to "set identity insert" to be able to
populate the identity column.

Instead of a new table, you MAY be able to get away with

- rename the old key column
- add the new identify
- set identity insert on
- update the identity column
- set identity insert off
- delete original key column

PLEASE NOTE: I have not actually tried this, but it should work in theory.

"Lyle H. Gray" <gr**@no.spam.cs.umass.edu.invalid> wrote in message
news:Xn**********************************@130.81.6 4.196...

gj*@o2.pl (grzes) wrote in news:e02bcd3.0411170538.5ab97764
@posting.google.com:

MS SQL Server 2000.
My case is: I have the table T with primary key calling __recid int
without identity property. This table includes a lot of records (about
1000000). I need to convert __recid's data type to IDENTITY.

As you know sql sentence: "alter table T alter column __recid int
IDENTITY not null" does not work with not-empty tables.
I use the SQL Enterprise Manager which can convert the field __recid
into identity but I need another way to solve the problem, probably I
should use TSQL. I don't know...

Easiest method is to create a new table with an IDENTITY column and copy
the rows from the old table into it. Then rename the tables appropriately
so that the new table has the old table's name.

Regards,
Lyle

[Erland Sommarskog]

David Rawheiser (ra*******@hotmail.com) writes:

Please note that you will have to "set identity insert" to be able to
populate the identity column.

Instead of a new table, you MAY be able to get away with

- rename the old key column
- add the new identify
- set identity insert on
- update the identity column
- set identity insert off
- delete original key column

PLEASE NOTE: I have not actually tried this, but it should work in theory.

It doesn't:

CREATE TABLE test (a int NOT NULL PRIMARY KEY)
go
INSERT test (a) VALUES (123)
go
ALTER TABLE test ADD b int IDENTITY
go
SET IDENTITY_INSERT test ON
go
UPDATE test SET b = a
go

yields:

(1 row(s) affected)
(1 row(s) affected)

Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'b'.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Rawheiser]

Thank you, I was rushed, so I didn't take time to research it, before
spouting off.

It still works in 'theory', just not in 'practice' - that whole reality
thing rears its ugly head.

I guess we need to submit an identity_update enhancement.

"Erland Sommarskog" <> wrote in message
news:Xn*********************@127.0.0.1...

David Rawheiser (ra*******@hotmail.com) writes:

Please note that you will have to "set identity insert" to be able to
populate the identity column.

Instead of a new table, you MAY be able to get away with

- rename the old key column
- add the new identify
- set identity insert on
- update the identity column
- set identity insert off
- delete original key column

PLEASE NOTE: I have not actually tried this, but it should work in
theory.

It doesn't:

CREATE TABLE test (a int NOT NULL PRIMARY KEY)
go
INSERT test (a) VALUES (123)
go
ALTER TABLE test ADD b int IDENTITY
go
SET IDENTITY_INSERT test ON
go
UPDATE test SET b = a
go

yields:

(1 row(s) affected)
(1 row(s) affected)

Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'b'.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

David Rawheiser (ra*******@hotmail.com) writes:

Thank you, I was rushed, so I didn't take time to research it, before
spouting off.

It still works in 'theory', just not in 'practice' - that whole reality
thing rears its ugly head.

I guess we need to submit an identity_update enhancement.

Yeah. Or start using SQL Server CE. I'm not using SQL Server CE myself,
but apparently you can use ALTER TABLE to change a column to IDENTITY in
SQL Server CE. Funny, isn't it?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp