Identity incriments when insert failed
-
Inserting data into a table with identity(decimal datatype) starting with 1 incriment by 1
When ever insert fails because of primary key in other colums, Identity incriments. This is a missing number.
Iam using VB program to insert the data. I would like to stop the increment of identity when insert fails.
Could any one help me in this .
-
The IDENTITY property cannot guarantee no gaps. You'll have to use a trigger, I think.
-
What business problem is this causing you?
-
Because of missing identities , I may run out of the scope.
-
What is the data type of your identity?
if its an integer than the max value is over 2 billion
That will take a long time to run out of numbers, even on the highest transaction tables.
and an identity will never be guaranteed to be sequential, There is no way to "Pack" these numbers.
I am not sure your concern should be an issue
-
The only time I saw this be an issue is that we are required by law to have a sequential number for our work orders reports (without missing ids). If your situation is not like that, then it's not a problem. Also remember that you can always change that column to a bigint IF needed.
-
Not pleasant looking I admit but the following logic (or some variant of it) may do what you want:
insert into myTable (id) select coalesce(max(id)+1,0) from myTable
The coalesce is required simply to get the first id in correctly when max() would return NULL.
Any further suggestions or improvements welcome
Just thought of another setback for you (sorry
). What happens if/when records are deleted? How are you planning to keep the values sequential then?HTH
-
If for some reason the numbers really must be sequential,(and as others have said, this is rare) then you could generate one in your VB code, or a trigger, maybe in conjunction with another table whose purpose is just to hold the next valid number. Perhaps you could look again at the table design, too - use the Identity field as an invisible primary key, and the VB generated, guaranteed sequential number as the visible 'invoice number' or whatever it is in your app.
It's often thought best not to have any 'meaning' in your PK anyway - it should just be a means of identifying the row without any other significance.
HTH
pg
-
In that situation IDENTITY should not be used. If it should ever be used.
-
I like that if Chris
. -
Have you thought about rebuilding the app to not depend on this identity? That would be the first choice. Failing this, and believe it - that is by far the best option, you could do what I was forced to do rather than rewrite the whole app base for my last company...
ALTER Procedure spRBLD_rebuildIndex
AS
DECLARE @NewSeed AS INT
DECLARE @sSQL AS VARCHAR(500)
SELECT @NewSeed = MAX(ID) FROM tblTest
SET @sSQL = 'DBCC CHECKIDENT(tblTest, RESEED, ' + Convert(Varchar(25),@Newseed) + ')'
EXEC(@sSQL)
GO
Execute this Proc before, and possibly after each insert into the table. You will only need to do it after if there are more than one way (read programs) that will insert data into this table.
If you have a procedure that you execute to insert records - that would be the best place for this. Anyway - I cannot guarantee that this will be 100% effective, although it never failed while I was at my last company - even with my trying to get it to fail so it would become a "necessity" to rewrite the code base, it is possible that if the CheckIdent console command failed - it would not reindex the table, causing a gap. Theoretically possible.
Hope this helps - although I hesitate to say putting that code into production is a "Help". Again - I beseige you to rewrite rather than use this.
Man - that makes ident fixes, built sql statements and cursors in the past few days... All my favorites...
NOT.Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com -
One way to avoid receiving an error for a Primary Key violation is to test the insert data to see if the Primary Key already exists in the table. If it does, then you could write the row to a log table and continue on with your processing. This could be done in a couple of ways.
if Not Exists(select 1 from <TableName> where PK = <PKValue>
insert into <TableName>
else
Insert into <LogTable>
OR
insert into <TableName>
select <Insert Data>
from <Insert source>
where <PKData> not in(select <PKValues> from <TableName>
It all depends on the context of the application as to which method would be better.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply