November 22, 2011 at 1:34 am
Hi,
I have a table with identity column and i have data in identity column from 1 to 10 and 20 to 50. 11 to 19 are deleted. if i insert a row into this table. i will get the identity as 51. But i need to insert it as 11. Can anybody help on this.
Regards,
Vaithilingam.K
November 22, 2011 at 1:57 am
Yes it can be done by setting IDENTITY_INSERT ON
November 22, 2011 at 2:31 am
Please follow the below steps:
1) SET IDENTITY_INSERT ON
2) insert the values using a INSERT Sql.
3) SET IDENTITY_INSERT OFF
Do remember to turn off the identity_insert to OFF again.
November 22, 2011 at 2:39 am
Keep in mind columns with the identity property are meant to generate their own value in a given sequence.
(check books online for seed and increment)
However - as replied by the others - , using set identity insert for a given table is an acceptable solution to support a load.
After such load, dbcc checkident should be ran so the engine knows which value to generate next.
Check books online DBCC CHECKIDENT for the options and usage info.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply