Solved

Can you edit a field with Identity Specification active?

Posted on 2009-04-01
11
176 Views
Last Modified: 2012-05-06
If you have a field in a table with the identity specification set as "yes"...is there a way to edit that field in the tables?  For example purposes only, MyTable has a field named MyID with identity spec set to yes to start at 20090001 in increments of 1.  So one of the records in this table has a MyID of 20090010, but I want to change this MyID to 20080500.  Is there a way to do this, without writing the script to change the identity spec to no, make the change, and then run the script to set the identify spec back to yes?
0
Comment
Question by:Lee R Liddick Jr
  • 6
  • 3
  • 2
11 Comments
 

Author Comment

by:Lee R Liddick Jr
ID: 24042218
Increasing the point value because i need to know asap.  Anyone?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24042220
you cannot "update". but you can insert a copy of the record with a new identity value (check out SET IDENTITY_INSERT ON statement), and then delete the old record.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24042242
Okay that helps, but I've never done that before...can I do a search on the internet on how to prepare that script or do you have any suggestions on how that is written?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 24042420
here we go:
http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx


declare @old_id int

declare @new_id int
 

set @old_id = 20090010 

set @new_id = 20080500
 

set identity_insert MyTable on 

begin transaction
 

insert into MyTable (id, col1, col2 )

 select @new_id, col1, col2

   from Mytable where id = @old_id
 

if @@error = 0

begin

  delete MyTable where id = @old_id

  if @@error = 0

    commit

  else

    rollback

end

else

begin

  rollback

end
 

set identity_insert MyTable off 

Open in new window

0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24042479
The script below shows how to do it.  I created a tmp table, t2, with MyId as an identity like yours and populated it with some records, then used this script to move one of then like you described.  Of course a record with 20080500 can not already exist.
SET IDENTITY_INSERT t2 ON

INSERT INTO t2 (MyId, GroupId, Lvl, Content)

SELECT 20080500, GroupId, Lvl, Content FROM t2 WHERE MyId = 20090010

SET IDENTITY_INSERT t2 OFF

DELETE FROM t2 WHERE MyId = 20090010

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Lee R Liddick Jr
ID: 24043068
Excellent example...let me work this out and get back to assign points.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24043107
Well it worked on my development side because we have admin access to those databases/tables; however, on our production side we do not so it didn't work.  I'm going to have to go through our IT then to fix this.  Unless there is a way to fix this without having admin rights to the database/table.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24043162
Also, do you have to write this entire script for each record you need to change?  I have 30 that needs changed...can I do that in one script somehow?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24043258
you need the set identit_insert only once fo the entire script.

if you have a table/list of the ID mappings, you can "shortcut" the code, however, as this sounds like one-shot, I would not bother...
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 250 total points
ID: 24044717
You could make a quick stored procedure and call it the 30 times to make the script cleaner and less chances to mess something up with cut and pasting 30 times.
Again you will have to get someone with proper permissions on production to do it for you since you do not have those permissions.
If you accept this answer, make sure angelIII gets some credit, I used his example since he had transaction coding in it already.
create procedure udfMoveIdentityRecords

 @old_id int,

 @new_id int

as 

 

set identity_insert MyTable on 

begin transaction

 

insert into MyTable (id, col1, col2 )

 select @new_id, col1, col2

   from Mytable where id = @old_id

 

if @@error = 0

begin

  delete MyTable where id = @old_id

  if @@error = 0

    commit

  else

    rollback

end

else

begin

  rollback

end

 

set identity_insert MyTable off 

go
 

-- repeat just this line for each id change 

exec udfMoveIdentityRecords @old_id = 20090010, @new_id = 20080500
 

drop procedure udfMoveIdentityRecords

Open in new window

0
 

Author Closing Comment

by:Lee R Liddick Jr
ID: 31565445
My apologies for not awarding these points sooner...I guess I was all excited everything worked that I forgot.  Thank you both for the assistance!!!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 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

18 Experts available now in Live!

Get 1:1 Help Now