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]

Re: sql server -- identity problem

gjo@o2.pl (grzes) wrote in news:e02bcd3.04 11170538.5ab977 64
@posting.google .com:
[color=blue]
> 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...[/color]

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]

Re: sql server -- identity problem

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" <gray@no.spam.c s.umass.edu.inv alid> wrote in message
news:Xns95A457F 33CC3Egraynoibi sspamcsumas@130 .81.64.196...[color=blue]
> gjo@o2.pl (grzes) wrote in news:e02bcd3.04 11170538.5ab977 64
> @posting.google .com:
>[color=green]
>> 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...[/color]
>
> 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[/color]

[Erland Sommarskog]

Re: sql server -- identity problem

David Rawheiser (rawhide58@hotm ail.com) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Rawheiser]

Re: sql server -- identity problem

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:Xns95A84B2 D2C6EYazorman@1 27.0.0.1...[color=blue]
> David Rawheiser (rawhide58@hotm ail.com) writes:[color=green]
>> 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.[/color]
>
> 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, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: sql server -- identity problem

David Rawheiser (rawhide58@hotm ail.com) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp