February 4, 2011 at 5:17 am
Hello friends,
i am having a table with no identity
like:
CREATE TABLE [dbo].[Demo2](
[id] [int] NULL,
[address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[rid] [int] NULL
) ON [PRIMARY]
now i want to update id column to an identity column...
please send sql statement to do the same
Thanks!!
February 9, 2011 at 2:34 pm
you can't change it to an identity column as is. you can rename the table, create a new one with the identity column and then insert the old records into the new table.
exec sp_rename 'Demo2', 'Demo2Old'
CREATE TABLE [dbo].[Demo2](
[id] [int] identity (1,1) not NULL,
[address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[rid] [int] NULL
) ON [PRIMARY]
insert into Demo2 (address, rid)
select address, rid from Demo2Old
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 2:41 pm
To extrapolate on what Mike provided above, you can't edit an IDENTITY column while it's being included, and you can't update it after the fact.
You can, however, INSERT into it with the IDENTITY_INSERT option, which he didn't provide.
For starters, perform the rename and re-declaration tasks he provided above.
Then use the command SET IDENTITY_INSERT Demo2 ON
This will allow you to declare the identity column during the INSERT INTO () statement, allowing you to keep your old identities, but in the new, auto-numbering column.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply