Solved

Cannot Create Identity Key by Altering a Column with Primary Key

Posted on 2011-03-03
2
252 Views
Last Modified: 2012-05-11
Hi,

I have a table created with primary key not null and it has data in it.
Later on i realized the primary key column should be identity (1,1).

I have to do it using the scripts not going to the design view.

ALTER TABLE XYZ
ALTER COLUMN [ID] [int] IDENTITY(1,1) NOT NULL;

i get the following error, any help will be appreciated.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IDENTITY'.

Thanks,
Rohan

0
Comment
Question by:Star79
2 Comments
 
LVL 7

Accepted Solution

by:
kemi67 earned 250 total points
ID: 35028331
You can't alter the existing columns for identity.
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


This is an example for a table XYZ and fileds ID and Fielda

 
/* Per evitare potenziali problemi di perdita di dati, si consiglia di esaminare dettagliatamente lo script prima di eseguirlo al di fuori del contesto di Progettazione database.*/
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_XYZ
	(
	ID int NOT NULL IDENTITY (1, 1),
	fielda nchar(10) NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_XYZ ON
GO
IF EXISTS(SELECT * FROM dbo.XYZ)
	 EXEC('INSERT INTO dbo.Tmp_XYZ (ID, fielda)
		SELECT ID, fielda FROM dbo.XYZ WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_XYZ OFF
GO
DROP TABLE dbo.XYZ
GO
EXECUTE sp_rename N'dbo.Tmp_XYZ', N'XYZ', 'OBJECT' 
GO
COMMIT

Open in new window


other examples here
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032/
0
 
LVL 2

Assisted Solution

by:Michael-Thomas
Michael-Thomas earned 250 total points
ID: 35076150
kemi67 is correct.  

You can generator your own SQL from SSMS by going to design on your table, changing the ID field to identity then choose the save script option from the icons at the top left.


If you need to reseed the identity value you can use this

declare @maxID int
select @maxID = max(ID) from XYZ
DBCC CHECKIDENT (XYZ, reseed, @MaxID)

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Grid querry results 41 82
Caste datetime 2 69
Help Required 2 46
Database maintenance 36 101
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question