Identity Removal for the Existing Data Table

The below solution will work if you have Enterprise SQL Server 2008 --create one table with identity column


--create one table with identity column

CREATE TABLE IdentityDemo

(

ID INT IDENTITY(1,1),

NAME VARCHAR(10)

)



--insert few records for testing

INSERT INTO IdentityDemo

SELECT 'Revathi' UNION ALL

SELECT 'Rathiga'



--checking the records

SELECT * FROM IdentityDemo



--creating one table with same structure but without identity

CREATE TABLE IdentityDemo2

(

ID INT NOT NULL,

Name VARCHAR(10)

)



--using swich concept of partition

ALTER TABLE IdentityDemo SWITCH TO IdentityDemo2

--droping original table

DROP TABLE IdentityDemo

--renaming duplicate table with original table's name

EXEC sp_rename 'IdentityDemo2','IdentityDemo'



--checking finally

SELECT * FROM IdentityDemo




/*************************************************/

The below article suggests to alter the identity column through SSMS management studio and script out the scripts.

http://www.mssqltips.com/sqlservertip/1397/add-or-drop-identity-property-for-an-existing-sql-server-column/