Solved

SQL Identity column changed

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now