Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set identity column to created table in SQL server

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 :)

like image 786
zey Avatar asked Apr 24 '13 09:04

zey


People also ask

Can we add identity column after creating the table?

Only one identity column can be created per table.

How do you add an identity column to a 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.


1 Answers

ALTER TABLE [UserName] DROP COLUMN [ID];

ALTER TABLE [UserName] 
    ADD [ID] integer identity not null;
like image 87
a_horse_with_no_name Avatar answered Oct 24 '22 00:10

a_horse_with_no_name