Adding/Removing IDENTITY property on a Column  

Posted by ReelTym

CREATE TABLE MyTable ( ID INT, Data varchar(max) )
GO
INSERT INTO MyTable VALUES ( 4, 'This is stuff' ), ( 6, 'This is more stuff' )
GO
SELECT * FROM MyTable
GO

--------
-- Adding IDENTITY property
--------
SELECT * INTO #MyTempTable FROM MyTable
GO
TRUNCATE TABLE MyTable
GO
ALTER TABLE MyTable ADD ID_II INT IDENTITY(1,1)
GO
ALTER TABLE MyTable DROP COLUMN ID
GO
ALTER TABLE MyTable ALTER COLUMN ID_II INT NOT NULL
GO
EXEC sp_rename 'MyTable.ID_II', 'ID', 'COLUMN'
GO
SET IDENTITY_INSERT MyTable ON
GO
INSERT INTO MyTable ( ID, Data ) SELECT t.ID, t.Data FROM #MyTempTable t
GO
SET IDENTITY_INSERT MyTable OFF
GO
INSERT INTO MyTable ( Data ) VALUES ( 'This is the last stuff' )
GO
DROP TABLE #MyTempTable
GO

SELECT * FROM MyTable
GO
EXEC sp_help MyTable
GO

--------
-- Removing IDENTITY property
--------
-- Drop FKs/UKs/PKs/Indexes that reference the Column being changed.
ALTER TABLE MyTable ADD ID_II INT
GO
UPDATE MyTable SET ID_II = ID
GO
ALTER TABLE MyTable DROP COLUMN ID
GO
ALTER TABLE MyTable ALTER COLUMN ID_II INT NOT NULL
GO
EXEC sp_rename 'MyTable.ID_II', 'ID', 'COLUMN'
GO
-- Recreate FKs/UKs/PKs/Indexes that referenced the Column being changed.

SELECT * FROM MyTable
GO
EXEC sp_help MyTable
GO

--Adding IDENTITY property to a Column
DROP TABLE MyTable
GO

This entry was posted on Friday, January 28, 2011 and is filed under . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments