How to remove identity in a column through query
Barry Alllen ~
Modified: April 22nd, 2016 ~ ~ 1 Minute Reading
- This topic has 4 replies, 4 voices, and was last updated 8 years, 5 months ago by .
Viewing 5 posts - 1 through 5 (of 5 total)
-
Posts
-
You cannot remove an IDENTITY specification once set. You need to remove the entire column
• Create a new column
• Transfer the data to the new column
• Drop old column
• Rename new column to the old column name- Create a new column
- Copy values over
- Drop the old column
- Rename new column to the old column name
alter table users add newusernum int;update users set newusernum=usernum;alter table users drop column usernum;EXEC sp_RENAME 'users.newusernum' , 'usernum', 'COLUMN';Hope it will help you
BEGIN TRAN BEGIN TRY EXEC sp_rename '[SomeTable].[Id]', 'OldId'; ALTER TABLE [SomeTable] ADD Id int NULL EXEC ('UPDATE [SomeTable] SET Id = OldId') ALTER TABLE [SomeTable] NOCHECK CONSTRAINT ALL ALTER TABLE [SomeTable] DROP CONSTRAINT [PK_constraintName]; ALTER TABLE [SomeTable] DROP COLUMN OldId ALTER TABLE [SomeTable] ALTER COLUMN [Id] INTEGER NOT NULL ALTER TABLE [SomeTable] ADD CONSTRAINT PK_JobInfo PRIMARY KEY (Id) ALTER TABLE [SomeTable] CHECK CONSTRAINT ALL COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN SELECT ERROR_MESSAGE () END CATCH
Viewing 5 posts - 1 through 5 (of 5 total)
- You must be logged in to reply to this topic.