Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
patshaw
Posting Yak Master
177 Posts |
 Posted - 2007-01-16 : 07:21:27
|
| Hi All,Someone at my work has just deleted a row of data from a Users table and re-inserted the same details on a new row with a new AutoID on the PK field (it wasn't me honest).This has severely messed up our reporting on this agent as everything is linked to the original (now deleted) ID. Is there any way I can reinstate this single ID to basically take the data in the new row and change the primary key value to the old deleted value? Is this possible?Thanks. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-16 : 07:38:31
|
| SET IDENTITY <TableNameHere> {ON | OFF}UPDATE ...SET IDENTITY <TableNameHere> {ON | OFF}Peter LarssonHelsingborg, Sweden |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-16 : 07:41:59
|
| Do you mean IDENTITY_INSERT Peter?I believe that does not work with UPDATE, it's only to add new records with custom identity values. Of course, you can simulate update as Delete followed by the Insert.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-16 : 07:45:05
|
| Yes, Harsh. I knew something was missing from the suggestion above.SET IDENTITY_INSERT <TableNameHere> {ON | OFF}INSERT ...DELETE ...SET IDENTITY_INSERT <TableNameHere> {ON | OFF}Thank you.Peter LarssonHelsingborg, Sweden |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-16 : 08:47:49
|
| Advice: use referential integrity in your database with properly configured foreign key constraints and this stuff won't happen. Just making it a "convention" that an ID in certain tables matches ID's in other tables gives you no guarantee that your data is correct; you must ensure that things are properly constrained by your database by using the features available.- Jeff |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2007-01-16 : 09:14:20
|
| Thanks guys,IDENTITY_INSERT worked perfectly.jsmith8858 - Yes, I appreciate your advice but unfortunately many developers have had their hands on this DB and it was created long before I began work here............... I'm sure you know the rest! |
|
|
|
|
|
|
Change Primary Key ID on 1 row