Duplicate key error

I have a table that I use an INSERT command to add a new record. The
primary key is an int field and I am calculating the highest # on table and
incrementing by 1 just before INSERT. On rare occasions, 2 people get the
same number and I get SQL error about duplicate. Is there a way to trap
this or prevent it and just assign a number 1 higher and try INSERT again
(note I cannot use Identity)? Thanks.
David"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl...
>I have a table that I use an INSERT command to add a new record. The
>primary key is an int field and I am calculating the highest # on table and
>incrementing by 1 just before INSERT. On rare occasions, 2 people get the
>same number and I get SQL error about duplicate. Is there a way to trap
>this or prevent it and just assign a number 1 higher and try INSERT again
>(note I cannot use Identity)? Thanks.
>
Which is exactly like saying "I am having trouble driving this nail with my
screwdriver (note I cannot use a hammer)."
IDENTITY is the right tool for the job. Everything else is an inferior
workaround. Now to get your inferior workaround to actually work, you must
wrap the select max(ID) and INSERT in a serializable transaction. Or
something like
BEGIN TRANSACTION
SELECT @.ID = MAX(ID) from T (tablockx,holdlock)
insert T (ID,...) valueS (@.ID,...)
COMMIT TRANSACTION
BTW, you can perhaps use an IDENTITY column on a different table to generate
your ID's, eg
http://groups-beta.google.com/group...bcc24
68
David|||David,
How are you doing this?
AMB
"David C" wrote:

> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on table an
d
> incrementing by 1 just before INSERT. On rare occasions, 2 people get the
> same number and I get SQL error about duplicate. Is there a way to trap
> this or prevent it and just assign a number 1 higher and try INSERT again
> (note I cannot use Identity)? Thanks.
> David
>
>|||Thank you. I agree, I always use Identity but have to wait for an old
conversion before I can change it to an identity. BTW, once I make it an
Identity field will it automatically set the SEED, or will I have to do that
with DBCC?
David
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e86uLq6DFHA.3928@.TK2MSFTNGP15.phx.gbl...
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl...
> Which is exactly like saying "I am having trouble driving this nail with
> my screwdriver (note I cannot use a hammer)."
> IDENTITY is the right tool for the job. Everything else is an inferior
> workaround. Now to get your inferior workaround to actually work, you
> must wrap the select max(ID) and INSERT in a serializable transaction. Or
> something like
> BEGIN TRANSACTION
> SELECT @.ID = MAX(ID) from T (tablockx,holdlock)
> insert T (ID,...) valueS (@.ID,...)
> COMMIT TRANSACTION
> BTW, you can perhaps use an IDENTITY column on a different table to
> generate your ID's, eg
> http://groups-beta.google.com/group...bcc
2468
> David
>|||David C wrote:
> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on
> table and incrementing by 1 just before INSERT. On rare occasions, 2
> people get the same number and I get SQL error about duplicate. Is
> there a way to trap this or prevent it and just assign a number 1
> higher and try INSERT again (note I cannot use Identity)? Thanks.
> David
You need to keep things locked in a transaction in order to prevent
users from stepping on one another. I generally prefer using another key
table that dishes out next key values if an identity can't be used.
In your case, you need to do something like this:
Begin Tran
Select @.NextID = MAX(ID) + 1 From TableA WITH (UPDLOCK, HOLDLOCK)
Insert TableA
Commit Tran
David Gugick
Imceda Software
www.imceda.com|||David,
You can also use a subquery in your INSERT statement, in this case you
don't need to use a transaction.
Instead of this:
insert MyTable values(ID, Col2, Col3, ..., ColN)
use this:
insert MyTable
select ID = IsNull((select max(ID+1) from MyTable), 1),
Col2,
Col3,
..,
ColN
Shervin
"David C" <dlchase@.lifetimeinc.com> wrote in message news:<ufJVuh6DFHA.2804@.TK2MSFTNGP14.ph
x.gbl>...
> I have a table that I use an INSERT command to add a new record. The
> primary key is an int field and I am calculating the highest # on table an
d
> incrementing by 1 just before INSERT. On rare occasions, 2 people get the
> same number and I get SQL error about duplicate. Is there a way to trap
> this or prevent it and just assign a number 1 higher and try INSERT again
> (note I cannot use Identity)? Thanks.
> David|||You'll need to recreate the table if you need to convert the existing values
to IDENTITY. You can't add the IDENTITY property to an existing column.
Hope this helps.
Dan Guzman
SQL Server MVP
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:eHQjdv6DFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Thank you. I agree, I always use Identity but have to wait for an old
> conversion before I can change it to an identity. BTW, once I make it an
> Identity field will it automatically set the SEED, or will I have to do
> that with DBCC?
> David
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:e86uLq6DFHA.3928@.TK2MSFTNGP15.phx.gbl...
>|||> You can also use a subquery in your INSERT statement, in this case you
> don't need to use a transaction.
You can't prevent the assignment of duplicate values without a transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:4eaef17a.0502101720.4d2016f@.posting.google.com...
> David,
> You can also use a subquery in your INSERT statement, in this case you
> don't need to use a transaction.
> Instead of this:
> insert MyTable values(ID, Col2, Col3, ..., ColN)
>
> use this:
> insert MyTable
> select ID = IsNull((select max(ID+1) from MyTable), 1),
> Col2,
> Col3,
> ...,
> ColN
> Shervin
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:<ufJVuh6DFHA.2804@.TK2MSFTNGP14.phx.gbl>...|||Dan,
You are absolutely right. I just tested my code and it failed. I
thought SQL Server would automatically start a transaction to protect
the execution of subquery, but I was wrong. Thanks for correcting me.
Regards,
Shervin
Dan Guzman wrote:
you
> You can't prevent the assignment of duplicate values without a
transaction.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
> news:4eaef17a.0502101720.4d2016f@.posting.google.com...
you
The
table
get
to trap
INSERT again