How to Add Identity to Existing Column in Sql Server

Question

1.20/5 (3 votes)

See more:

SQL

alter column id int not null Identity(1,1)..

Its giving me an error as Incorrect syntax near the keyword 'Identity'.

Posted 14-Aug-14 1:56am

Vishal.Singh879

Updated 19-Apr-17 22:55pm

Add a Solution

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

krishnaMurali · Answer 1 · 2014-08-14T02:43:00

Solution 5

first run this

SQL

Alter TABLE [dbo].[t_name]
    drop column [id]

then run this

SQL

Alter TABLE [dbo].[t_name]

add  [id] [int] IDENTITY(1,1) NOT NULL

Posted 14-Aug-14 2:43am

krishnaMurali

W∴ Balboos, *GHB* · Answer 2 · 2014-08-14T02:17:00

Solution 1

Normally, I use the SQL Server Management Studio. I can simply toggle IDENTITY on and off. Lacking this, one solution is to add a new column and then drop your old one.

SQL

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

.

Posted 14-Aug-14 2:17am

W∴ Balboos, GHB

Updated 14-Aug-14 2:19am

v2

Comments

Vishal.Singh879 14-Aug-14 8:23am

i have to add identity to my existing column

Peter Leow · Answer 3 · 2014-08-14T02:38:00

Solution 3

Refer: adding-an-identity-to-an-existing-column[^]

Posted 14-Aug-14 2:38am

Peter Leow

Magic Wonder · Answer 4 · 2014-08-14T02:39:00

Solution 4

Hi,

Check this...

How to alter column to identity[^]

Hope this will help you.

Cheers

Posted 14-Aug-14 2:39am

Magic Wonder

Mangesh4 · Answer 5 · 2014-08-14T02:19:00

Solution 2

Hi Vishal

try this

SQL

alter table t_name
alter  id int identity(1,1)

Thanks
Mangesh

Posted 14-Aug-14 2:19am

Mangesh4

v2

Comments

Vishal.Singh879 14-Aug-14 8:22am

Its not working

Tomasz.Wieczorkowski · Answer 6 · 2017-04-19T22:55:00

Solution 6

There are potential methods like below of solving this problem.

1. Drop and re-create table with INT IDENTITY column
2. Drop INT column and re-create it as an INT IDENTITY column
3. ALTER column with INT IDENTITY NOT NULL (only if there is no NULL values in it already eg. clean table)
4. Add new INT IDENTITY column to the table next to INT column and use such new column then.

Posted 19-Apr-17 22:55pm

Tomasz.Wieczorkowski

Comments

CHill60 20-Apr-17 5:30am

The question is over 2 years old and already had 5 solutions. All of your suggestions have already been covered, with the correct syntax.
Posting solutions to old questions can quickly lead to loss of reputation points and in extreme cases can lead to your account being suspended. Be very wary of doing it.