February 26, 2008 at 1:04 pm
I have a table with Identity Column, Can someone tell me how to maintain identity in the table even after deleting some rows in the middle.
February 26, 2008 at 1:07 pm
Deleting rows in a table with an identity value doesn't change the next identity value. The value is preserved, so the identity is maintained.
Unless you TRUNCATE TABLE or you use DBCC CHECKIDENT with a RESEED option, the identity will continue to increment.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 1:08 pm
the identity column should go on regardless of deletes
It doesnt reset if you are using an identity column
February 26, 2008 at 2:46 pm
...and if you're asking how to "renumber" the identity column so there are no gaps, I'd say you're wasting your time. The values in identity columns shouldn't be perceived to have any meaning. They're only use is to automatically generate an incremented, unique value, such as for a primary key.
Greg
February 28, 2008 at 11:59 am
Hey, I found a solution for this and here it is.
Problem:
I have a Staging Table which updates every week incrementally.
Per Business, in this table every week I have to delete the last week data and add current week and last week data. Because of deleting and adding records in a table, Identity column is having gaps between numbers. Identity column is integer and auto increment.
Solution:
1) Delete Last Week data in staging table
2) Get the max for IdentityColumn
3) Create Temporary table with Identity Column starting number is (max+1) and seed is 1
4) SET IDENTITY_INSERT dbo.StagingTable ON
5) Insert into Staging Table from Temporary Table including Identity Column
6)SET IDENTITY_INSERT dbo.StagingTable OFF
Let me know if you have a better solution than this
February 28, 2008 at 12:12 pm
I'm not sure what yuo're using this table for - but just shuffling the identity fields around usually causes a LOT of throuble.
Reusing identity numbers previously used is a relational integrity disaster just waiting to blow up in your face. Under your model Client 1234 one week will be 222 next week, and someone entirely different might be 1234. How you plan on keeping that straight is a huge undertaking.
Identity fields are NOT guaranteed to not ever have gaps. This is not Excel - deleting a row isn't going to "move all of the rows up".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 5:27 pm
I know primary key and foreign key relationship among tables and here my table doesn’t have relation ship with other tables on Identity Column. It is a Staging table.Purpose of this table is to pull the data from other non sql server database and then do the need-ful from there.
February 28, 2008 at 6:12 pm
then perhaps just try dropping and re-adding an identity column. All of the numbers will regenerate.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 11:23 pm
Yes, you are correct that is one way of doing.
I want to do add or drop column in a stored procedure.
When you add a column programmatically it will add at the bottom, you cannot add the column at a specified position.
To maintain consistency with other tables I am maintaining Identity column as first column in staging table too
February 28, 2008 at 11:41 pm
Hi
Without wanting to sound rude..
What do you mean this "To maintain consistency with other tables I am maintaining Identity column as first column in staging table too "
"Keep Trying"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply