You are here:Home » tsql » Example of Changing a Non-IDENTITY column to IDENTITY and vice versa

Example of Changing a Non-IDENTITY column to IDENTITY and vice versa

ALTER TABLE SOURCE_TB ADD ID_II INT IDENTITY(1,1)
ALTER TABLE SOURCE_TB DROP COLUMN ID
EXEC sp_rename 'SOURCE_TB.ID_II' ,'ID','COLUMN'


CREATE TABLE DESTINATION_TB
(
ID INT IDENTITY(125006,1) NOT NULL
,DATE DATETIME
,COST MONEY
)

INSERT INTO DESTINATION_TB (ID, DATE, COST) SELECT ID ,DATE, COST FROM SOURCE_TB
DROP TABLE SOURCE_TB
EXEC sp_rename 'DESTINATION_TB' ,'SOURCE_TB'



CREATE TABLE DESTINATION_TB
(
ID INT IDENTITY(125006,1) NOT NULL
,DATE DATETIME
,COST MONEY
)
ALTER TABLE SOURCE_TB SWITCH TO DESTINATION_TB
DROP TABLE SOURCE_TB
EXEC sp_rename 'DESTINATION_TB' ,'SOURCE_TB'