Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2007-08-27 : 11:32:47
|
| Guys,I have following table where I want to add identity property to existing column is this possible??create table [identity] (id int not null, fname varchar(10))ALTER TABLE [dbo].[identity] ADD CONSTRAINT [PK_identity] PRIMARY KEY NONCLUSTERED ( [ID] ASC)insert into [identity] values (1, 'smith')below statement gives me error is there any way to accomplish this alter table [identity] alter column id int identity (1, 1)not nullAny suggestions and inputs would help.Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-27 : 11:37:15
|
| If the table does not already have an identity column, you can add a NEW column with IDENTITY, drop the ID column you want to convert to Identity column, rename the new column to ID.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Jenda
Starting Member
29 Posts |
Posted - 2007-08-27 : 11:39:34
|
| You'll have to create a new table and copy the data over with "SET IDENTITY_INSERT ON.Make the change in the Management Studio (raise your hands all who think the "Enterprise Manager" in MSSQL2000 was better) and ask the Studio to generate the change script for you, that's generally the easiest solution ... |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-27 : 11:43:20
|
quote:
Originally posted by Jenda
You'll have to create a new table and copy the data over with "SET IDENTITY_INSERT ON.Make the change in the Management Studio (raise your hands all who think the "Enterprise Manager" in MSSQL2000 was better) and ask the Studio to generate the change script for you, that's generally the easiest solution ...
No need to create a new table for this change.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-27 : 12:55:10
|
quote:
Originally posted by Jenda
... (raise your hands all who think the "Enterprise Manager" in MSSQL2000 was better) ...
i see no hands up  now query analyzer i'd understand but not enterprise manager._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
Setting identity property on an existing column