Solved

SQL Identity column changed

Posted on 2013-12-31
5
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

636 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