Solved

Is there a way to manually change/update  an ID number in SQL Server Management Studio

Posted on 2010-11-18
8
264 Views
Last Modified: 2012-05-10
The ID number are auto-incremental and some rows were deleted so there are numbers in between where there are a couple of instances were I would like to change one or two of those ID numbers for continuity but is grayed out. Is there a way to do this with the GUI interface?

For example I have rows

1
4
5
6
7

I would like to change the 4 to a 2 and the 5 to a 3.
ID is the primary key and is set to "identity".

Thank you in advance.
0
Comment
Question by:colonelblue
[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
  • 2
  • 2
  • +1
8 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 375 total points
ID: 34166554
Hi,

You can do that by

DBCC CHECKIDENT('YourTableName', RESEED, 1)
0
 
LVL 29

Assisted Solution

by:sammySeltzer
sammySeltzer earned 125 total points
ID: 34166558
use identity reseed

DBCC CHECKIDENT (yourtable, reseed, startnumber)


0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 375 total points
ID: 34166579
Hi,

As you said its PK so if other table relates to this table then you need to check integrity of FK.

either you need to update id of FK table.

or need to change cascade relations.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:colonelblue
ID: 34169298
Thank you both. If you don't mind me asking since I am more reliant on using the GUI ( For example using the diagrams, dropdown boxes, clicks ) because I am a newb and wouldn't even know where to run that code for a single row, is there a way to do it using the interface?
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 375 total points
ID: 34169872
Hi.

Just paste one of the code in query window.

Give table name where i write ur tablename.

Select database from database list which u want to use.

If you wanted to do via GUI then open ur table in design mode,first set auto identity to No for id column.

2nd save the table

3rd again set yes to auto identity column.

I knw u r new to sql but try 2 do,if u stuck,we are here to help u.

I suggest go with 1st option.
0
 

Author Closing Comment

by:colonelblue
ID: 34170482
Thank you especially Brichsoft.
You guys are awesome.
0
 
LVL 22

Expert Comment

by:dportas
ID: 34171712
Only use the GUI in this way if you have a backup or don't mind losing the data. Also make sure there is no-one else using the database. Using the GUI to modify tables like this can sometimes render the whole database ususable.

I strongly recommend you learn to use T-SQL so that you can write and execute SQL statements properly - that way you will have much more control over what you are doing.

IDENTITY columns have the irritating "feature" that they don't permit updates on the column. The easiest way to get around this may be to delete the rows and reinsert them.

The new SQL Server 2011 supports sequences, which makes it easier to create a surrogate key that you can update.
0
 
LVL 22

Expert Comment

by:dportas
ID: 34171719
Correction: "ususable" = "unusable"
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Express connecting form remote error 26 7 65
Set the max value for a column 7 43
Query to return total 6 26
Using this function 4 54
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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