Solved

Cannot Create Identity Key by Altering a Column with Primary Key

Posted on 2011-03-03
2
251 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 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