Solved

SQL Identity column changed

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server How-To Show Notes In First Row of Results 4 30
Query for timesheet application 3 17
SQL USE DATABASE VARIABLE 5 27
Are triggers slow? 7 11
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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