Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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.
 
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
Comments
pkarthionline at 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 at 4-Oct-12 10:38am
   
Because identity cannot be set at alter column http://msdn.microsoft.com/pl-pl/library/ms190273.aspx
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

please modified this script as per your requirement to add identity column to you existing table.
 
/* 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[^]
  Permalink  
v2
Comments
gvprabu at 7-Oct-13 11:51am
   
nice example
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try this:
DBCC CHECKIDENT ("tablename", RESEED, 10);
  Permalink  
Comments
Tejas Vaishnav at 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 at 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

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Guruprasad.K.Basavaraju 490
1 Sergey Alexandrovich Kryukov 316
2 Shai Vashdi 264
3 OriginalGriff 250
4 praveen_07 115
0 Sergey Alexandrovich Kryukov 9,185
1 OriginalGriff 5,315
2 Peter Leow 4,040
3 Maciej Los 3,535
4 Abhinav S 3,308


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 5 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid