CODEWITHSUNDEEP
sql-serversql-server-2008
Logeshwaran Veluchamy
Logeshwaran Veluchamy

Reputation: 181

Add a IDENTITY to a column in SQL SERVER 2008

create table stud(
Student_Id int primary key,
Student_Name varchar(30),
Student_surname varchar(12),
Student_Initial varchar(10))

I had created a table stud. Now i want to add Identity to Student_Id column using alter query

alter table stud alter column student_Id int identity

I get error as

Incorrect syntax near the keyword 'identity'.

Upvotes: 1

Views: 11727

Answers (5)

Meysam PH
Meysam PH

Reputation: 7

Syntax:

IDENTITY [ (seed , increment) ]

alter your table like as this: 

create table stud( 
Student_Id int IDENTITY(1,1) primary key,
Student_Name varchar(30), 
Student_surname varchar(12), 
Student_Initial varchar(10));

Upvotes: 0

Allan S. Hansen
Allan S. Hansen

Reputation: 4091

If Stud contains data, you could always make a shadow table, e.g. Stud2, which contains the Identity column, then run 

ALTER TABLE dbo.stud SWITCH TO dbo.stud2

Then you can reseed Stud2, drop Stud, and rename Stud2 to Stud.

That way you can keep the data while dropping/recreating the table with Identity.

Upvotes: 2

Oasis
Oasis

Reputation: 478

ALTER TABLE MyTable
  ADD ID INT IDENTITY(1,1) NOT NULL

Upvotes: 2

Amul Harad
Amul Harad

Reputation: 146

you can use below query to set identity 

CREATE TABLE [dbo].[stud](
    [Student_Id] [int] IDENTITY(1,1) NOT NULL,
    [Student_Name] [varchar](30) NULL,
    [Student_surname] [varchar](12) NULL,
    [Student_Initial] [varchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
    [Student_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF GO

Upvotes: -1

shree.pat18
shree.pat18

Reputation: 21757

You cannot make an already existing column as an IDENTITY column. Either you drop and recreate the table with the column marked as IDENTITY', or drop the column and add a newIDENTITY` column.

Upvotes: 3

Related Questions