alter Identity column in existing table Sql Server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,
PERSON table have 115 records here.
id |name
1 |aaa
2 |bbb
3 |ccc

.
.
.
115|abv

i need to alter PERSON table for column ID identity.

how to change ...
like this way.

SQL

alter table [PERSON]
alter column ID bigint not null Identity(1,1)

but i get this error
Incorrect syntax near the keyword 'Identity'.

Regards,
Karthikeyan,
Bangalore.

Posted 4-Oct-12 3:46am

pkarthionline

Add a Solution

Comments

pkarthionline 4-Oct-12 10:06am

This way also got error.

alter table [Person]
alter column ID bigint not null Identity(116,1)

Incorrect syntax near the keyword 'Identity'.

n.podbielski 4-Oct-12 10:38am

Because identity cannot be set at alter column
http://msdn.microsoft.com/pl-pl/library/ms190273.aspx

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

dimpledevani · Answer 1 · 2012-10-04T23:17:00

Solution 2

Try this:
DBCC CHECKIDENT ("tablename", RESEED, 10);

Posted 4-Oct-12 23:17pm

dimpledevani

Comments

Tejas Vaishnav 5-Oct-12 8:21am

It will work when there is already an identity column and reset its value to our specific value, in your case it will be 10.

dimpledevani 5-Oct-12 8:42am

well sorry but as far as i understood the question i thought he needed to reset the value so i gave this solution

Tejas Vaishnav · Answer 2 · 2012-10-04T22:35:00

please modified this script as per your requirement to add identity column to you existing table.

SQL

/* To prevent any potential data loss issues, you should review this script in detail 
before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_PERSON
	(
	ID int NOT NULL IDENTITY (1, 1),
	NAME varchar(25) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_PERSON SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_PERSON ON
GO
IF EXISTS(SELECT * FROM dbo.PERSON)
	 EXEC('INSERT INTO dbo.Tmp_PERSON (ID, NAME)
		SELECT ID, NAME FROM dbo.PERSON WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_PERSON OFF
GO
DROP TABLE dbo.PERSON
GO
EXECUTE sp_rename N'dbo.Tmp_PERSON', N'PERSON', 'OBJECT' 
GO
COMMIT

for more information please review this link
SQL Server Add Remove Identity[^]
OR
Sql Server Add Remove Identity 2[^]