Solved

SQL Identity column changed

Posted on 2013-12-31
5
299 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

735 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