alter an existing table in sql server 2008, add AUTOINCREMENT for existing primary key

Question

2.00/5 (1 vote)

See more:

Hi everybodyy,

I have a table in sql server 2008 I need to alter it to add a AutoIncrement for already existing primary key field in this table.

Please I need the script to execute this query.

Can anybody help?

thank you

Posted 12-Oct-11 22:39pm

nourbt

Add a Solution

4 solutions

Solution 1

SQL

CREATE TABLE Student
 (
  Id        INT NOT NULL,
  Name    VARCHAR(100) NOT NULL
  )
go
/*
Populating Customer table,
using same TEST_Sequence to generate the Id column,
which we used for Cutomer table in past
*/
INSERT Student(Id, Name)
VALUES
(NEXT VALUE FOR TEST_Sequence, 'Ram'),
(NEXT VALUE FOR TEST_Sequence, 'Rita'),
(NEXT VALUE FOR TEST_Sequence, 'Ron')
go
select * from Student

by this code may help you....
thanku

Posted 12-Oct-11 22:50pm

deepak kumar choudhary

Updated 12-Oct-11 22:52pm

v2

Comments

nourbt 13-Oct-11 4:52am

thank you anyway, but what i needed is to make this field autoincrement in its properties

Solution 4

i have one city table i copy city table to temp table sd and add column sid auto increment

SQL

select name into #sd from city
ALTER TABLE #sd
ADD sid int NOT NULL IDENTITY (1,1) PRIMARY KEY
select * from #sd
drop table #sd

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)

P.Salini · Accepted Answer · 2011-10-12T23:11:00

If you have ms sql srvr mgmt studio you can do it. You just have to do it manually, not in script or command form.

connect to your db

show the table/column names in object explorer window

right click on column name

select modify

in column properties tab

alter "identity specification"/"is identity" to YES

set increment/seed properties as desired

You can't alter the existing columns for identity through query

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

check this

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032[^]

Mehdi Gholam · Accepted Answer · 2011-10-12T23:00:00

Solution 2

Read the following on the subject :

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032[^]

Posted 12-Oct-11 23:00pm

Mehdi Gholam