Solved

SQL Identity column changed

Posted on 2013-12-31
5
297 Views
Last Modified: 2013-12-31
I have an ERP database where all tables have an identity field column. It appears that on one of the tables someone changed the characteristic of this column to not be identity. I need to change this column back to being an identity column. This table has data in it. What is the best way to switch it back to being an indentity column?
0
Comment
Question by:rwheeler23
  • 3
5 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 39748968
You can change the column back to identity via Management studio but you can't alter the existing columns for identity.

Assume your table name is 'MyTable', column Name is 'ID' that is primary key and named 'PK_MyTable', one solution is to create a new column with identity and drop the existing column:

ALTER TABLE dbo.MyTable
ADD ID_New INT IDENTITY(1, 1)
Go

ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [PK_MyTable]
ALTER TABLE MyTable DROP COLUMN ID
Go

EXEC SP_RENAME 'MyTable.ID_New', 'ID', 'Column'

GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)

Open in new window


The above solution is not a good idea if your table is related to other tables, you need to remove and add relations again while the ID_New column regenerates new values.
0
 

Author Comment

by:rwheeler23
ID: 39749010
Therein lies my problem. This table is related to others and when I am done I need this identity column have the same name as when it started. What do you think of this?

copy the entire contents of this table to a back up table
truncate the original table
change the indentity property back to identity
copy the backup table to the original table
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39749238
Use SSMS to do this.  It should take care of dropping/creating all the foreign keys as well as using transactions.  Don't rely on scripting it yourself (unless of course you are confident you can do a better job).

To be clear, I am not suggesting you make the change in the SSMS Table Designer, but rather generate the SQL Script and run it from a query window.  This will allow you to inspect the code and not be limited to the 30 second timeout when run from the Table Designer
0
 

Author Comment

by:rwheeler23
ID: 39749278
I think was caused this was someone somewhere along the way used integration services and did not pay attention to the fact that it will drop any identity fields by default. You are correct. This should be done in the query window.

I first tried it your way and then mine. Both were successful. Thanks.
0
 

Author Closing Comment

by:rwheeler23
ID: 39749279
Thanks for the tips. Someone can now go off to a New Year's party.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 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