I created a table in SQL Server like this:
CREATE TABLE [UserName]
(
[ID] [int] NOT NULL ,
[Name] [nvarchar] (50) NOT NULL ,
[Address] [nvarchar] (200) NULL
CONSTRAINT [PK_UserName] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO
If I want to make ID
an identity column, what do I need? Do I need to drop and create this table and set ID
to [ID] [int] IDENTITY(1,1) NOT NULL
.
By using drop and create, all of data from UserName
table are lost .
Is there another way to set IDENTITY COLUMN
to created table's column without losing data?
I use SQL Server 2008 R2 :)
Only one identity column can be created per table.
You can't alter the existing columns for identity. You have 2 options, Create a new table with identity & drop the existing table. Create a new column with identity & drop the existing column.
ALTER TABLE [UserName] DROP COLUMN [ID];
ALTER TABLE [UserName]
ADD [ID] integer identity not null;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With