BillPowell
asked on
Reset identity field in a table variable
I have a proc where I create a Table variable with one of the fields being INT Identity. During the procedure after the table is filled with data, I check for and delete duplicates. After this, given that there may be gaps in the Identity column, I need to reset all values in the identity column so theres no gaps. What are my options with doing this within a Table Variable. Can I Truncate the table variable after deleting all the records?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Would it be simpler to create the table without the Identity column and then create it after you have removed the duplicates?
ASKER
That is a neat trick. Never seen that before. That will come in handy in many places.
ASKER
Thanks, much appreciated.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Bookmarked.
never seen that trick before!!
where did you find that, Einstine98?
never seen that trick before!!
where did you find that, Einstine98?
You'd be surprised, this was introduced to me by one of my reports in my previous job (sometime in 2002/2003)... this guy has a VERY limited knowledge of virtually ANYTHING IT - mind you he was part of my IT team :-)
I always thought its awesome and because I used to frequently load data from multiple sources into one, this was a very nice way of scrubbing the duplicates and creating new IDs. :-) I'm glad it scored well with you guys :-)
I always thought its awesome and because I used to frequently load data from multiple sources into one, this was a very nice way of scrubbing the duplicates and creating new IDs. :-) I'm glad it scored well with you guys :-)
Okay, I thought of giving credit to their respectful owners... I found this article through google...
http://www.sqlteam.com/item.asp?ItemID=765
one mistake in the guys article, UPdate table set field = @variable, @variable = expression gives the exact same outcome as the variable is normally evaluated first (at least in all the cases I tried)... thoughts are appreciated.
http://www.sqlteam.com/item.asp?ItemID=765
one mistake in the guys article, UPdate table set field = @variable, @variable = expression gives the exact same outcome as the variable is normally evaluated first (at least in all the cases I tried)... thoughts are appreciated.