Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

Can you edit a field with Identity Specification active?

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
Lee R Liddick Jr
Asked:
Lee R Liddick Jr
  • 6
  • 3
  • 2
2 Solutions
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Increasing the point value because i need to know asap.  Anyone?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Lee R Liddick JrReporting AnalystAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Excellent example...let me work this out and get back to assign points.
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
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
 
Lee R Liddick JrReporting AnalystAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Lee R Liddick JrReporting AnalystAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now