| Author |
Topic |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2004-02-26 : 12:12:01
|
| If I do this, INSERT INTO tblProvider SELECT 1, --intProviderTypeID, LOC_ID, --txtProviderCode, LOCATION, --txtProviderName, 1, --intInternalProvider, @txtNote, --txtNote, 1, --intAcceptReferral, NULL, --txtTaxID, NULL --intTaxIDTypeID FROM FamilyServices.dbo.locationcan I do this: SET @intProviderID = @@IDENTITYif I know that only one record went into tblProvider?In other words, if I do an insert and select a value, can I just set a variable to the @@IDENTITY value if I know for certain that only one record got inserted?Thanks!Aj |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 12:27:37
|
| Yes you can. Even if it inserted more than one record, @intProviderID would still get set, but it would be set to the last identity value inserted.Tara |
 |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-02-26 : 12:50:42
|
| Thanks Tara. I get the impression that you have been doing this stuff for quite a while!Aj |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-26 : 13:24:15
|
| You might want to use SCOPE_IDENTITY() instead of @@identity. It's generally better to use for this situation.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 13:25:54
|
| Yes definitely!Tara |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-02-26 : 13:55:38
|
| So this:SET @intProviderID = SCOPE_IDENTITY() will always return the last value inserted? If so, that is perfect! Thanks!Aj |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 14:00:01
|
| @@IDENTITY and SCOPE_IDENTITY() are similar in that they both return identity values. The problem is that @@IDENITY could return you the wrong identity value. If you have a trigger on the table that does an insert, @@IDENTITY would reflect the identity value of the insert that the trigger did not the one that you did. So use SCOPE_IDENTITY() (even if you don't have a trigger on the table currenlty, you might have one in the future).Tara |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-02-26 : 14:02:35
|
| Well, and correct me if I am wrong, but if I am doing multiple inserts (yes I am writing another migration script) and I am setting variables to @@IDENTITY, if one of the inserts fails for some reason, @@IDENTITY will still hold the last value.Will the Scope do that?Aj |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 14:03:55
|
| Both will hold the last value. @@IDENTITY just might hold the last value from the trigger, if one exists that is.Tara |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2004-02-26 : 14:08:52
|
| Ok, I will burn Scope_Identity into my brain and begin using it!Thanks a ton!Aj |
|
|
|
|
Quick question