Hi,
I have a table:

TABLE1
-------------
ID int primary
FIELD1 varchar
FIELD2 varchar

and I want to insert this table with increment ID (max ID + 1).

When I use with 1 record, there is no problem:
------------------------------------------------
INSERT INTO TABLE1 (ID, FIELD1, FIELD2)
SELECT (SELECT MAX(ID) + 1 FROM TABLE1), 'AAA', 'BBB' FROM TABLE1 WHERE ID
IN (1)

When I use with more than 1 record, there is "dublicate key error":
------------------------------------------------
INSERT INTO TABLE1 (ID, FIELD1, FIELD2)
SELECT (SELECT MAX(ID) + 1 FROM TABLE1), 'AAA', 'BBB' FROM TABLE1 WHERE ID
IN (1, 2)

Is there any way?

PS: Please don't tell identity, I know identity property but *I DON'T WANT
USE*

Why do you use the WHERE clause?  Leave that out and you will get the "next"
ID.  It won't be scalable, though.

I have a table:

TABLE1
-------------
ID int primary
FIELD1 varchar
FIELD2 varchar

and I want to insert this table with increment ID (max ID + 1).

When I use with 1 record, there is no problem:
------------------------------------------------
INSERT INTO TABLE1 (ID, FIELD1, FIELD2)
SELECT (SELECT MAX(ID) + 1 FROM TABLE1), 'AAA', 'BBB' FROM TABLE1 WHERE ID
IN (1)

When I use with more than 1 record, there is "dublicate key error":
------------------------------------------------
INSERT INTO TABLE1 (ID, FIELD1, FIELD2)
SELECT (SELECT MAX(ID) + 1 FROM TABLE1), 'AAA', 'BBB' FROM TABLE1 WHERE ID
IN (1, 2)

Is there any way?

PS: Please don't tell identity, I know identity property but *I DON'T WANT
USE*

There is a way, using an auto-join.  See KB186133 for information on how to
sequentially number rows in a select statement.  You can then use that
technique to compute the offset from MAX for each row so that the new ID
values will be different.

Using SELECT MAX to find the next number is not really a good way to assign
ID numbers.  If you choose to use it, you must decorate the MAX source table
thus: WITH(UPDLOCK, HOLDLOCK).  The reason is pretty complicated, but here's
a summary.  If two transaction issue SELECT MAX at the same time, and then
attempt to issue an INSERT, it is possible for both transactions to obtain
the same MAX value, thus causing one to raise a duplicate key constraint
violation.  If you SELECT MAX WITH(UPDLOCK, HOLDLOCK), SQL Server attempts
to obtain an update range-lock on the table during the SELECT MAX.  Since
only one transaction can obtain an update lock on a resource, one of the
transactions in the above example will block until the other transaction
completes.  Note: If the SELECT MAX is not part of the INSERT statement,
then you must specify an explicit transaction.

IMPORTANT: it is not sufficient to set the transaction isolation level to
SERIALIZABLE, because that only causes shared range-locks to be obtained and
held, which can cause deadlocks because more than one transaction can obtain
shared range-locks on a resource.  In the above example, the SELECT MAX for
each transaction completes, and then a deadlock occurs because both
transactions are holding a shared range-lock and both transactions must
obtain an exclusive lock in order to perform the INSERT.


Show quoteHide quote

Brian
Actually , there is no difference between UPDLOCK and HOLDLOCK.



Show quoteHide quote

Yes there is, Uri. 

HOLDLOCK only applies shared range-locks, not update range-locks.  UPDLOCK only applies individual update locks, not update range-locks, both are needed to apply update range-locks.  While shared range-locks will prevent inserts by another transaction during SELECT MAX, they don't prevent a another transaction from also applying a shared range-lock on the same resource.  Update range-locks not only prevent inserts, but also prevent the deadlock caused by more than one transaction executing the INSERT...SELECT MAX query at the same time.

The following script illustrates the difference between WITH(UPDLOCK), WITH(HOLDLOCK), and WITH(UPDLOCK, HOLDLOCK).  Notice that only the last set, WITH(UPDLOCK, HOLDLOCK) applies a 'RangeS-U' lock.  WITH(HOLDLOCK) applies a 'RangeS-S' lock, and WITH(UPDLOCK) applies an individual 'U' lock.

USE pubs
GO

SET NOCOUNT ON -- don't want to clutter up the results

--make sure the transaction isolation level is set to the default so your results aren't skewed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN
PRINT 'NO DECORATION'
SELECT MAX(emp_id) FROM employee
EXEC sp_lock @@SPID
ROLLBACK

BEGIN TRAN
PRINT 'WITH(HOLDLOCK)'
SELECT MAX(emp_id) FROM employee WITH(HOLDLOCK)
EXEC sp_lock @@SPID
ROLLBACK

BEGIN TRAN
PRINT 'WITH(UPDLOCK)'
SELECT MAX(emp_id) FROM employee WITH(UPDLOCK)
EXEC sp_lock @@SPID
ROLLBACK

BEGIN TRAN
PRINT 'WITH(UPDLOCK, HOLDLOCK)'
SELECT MAX(emp_id) FROM employee WITH(UPDLOCK, HOLDLOCK)
EXEC sp_lock @@SPID
ROLLBACK

Results:

NO DECORATION

---------
Y-L77953M

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
53     5      0           0      DB                    S        GRANT
53     1      85575343    0      TAB                   IS       GRANT

WITH(HOLDLOCK)

---------
Y-L77953M

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
53     5      0           0      DB                    S        GRANT
53     5      405576483   2      PAG  1:124            IS       GRANT
53     1      85575343    0      TAB                   IS       GRANT
53     5      405576483   2      KEY  (ffffffffffff)   RangeS-S GRANT
53     5      405576483   0      TAB                   IS       GRANT
53     5      405576483   2      KEY  (dd0038ef247f)   RangeS-S GRANT

WITH(UPDLOCK)

---------
Y-L77953M

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
53     5      0           0      DB                    S        GRANT
53     5      405576483   2      PAG  1:124            IU       GRANT
53     1      85575343    0      TAB                   IS       GRANT
53     5      405576483   0      TAB                   IX       GRANT
53     5      405576483   2      KEY  (dd0038ef247f)   U        GRANT

WITH(UPDLOCK, HOLDLOCK)

---------
Y-L77953M

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
53     5      0           0      DB                    S        GRANT
53     5      405576483   2      PAG  1:124            IU       GRANT
53     1      85575343    0      TAB                   IS       GRANT
53     5      405576483   2      KEY  (ffffffffffff)   RangeS-U GRANT
53     5      405576483   0      TAB                   IX       GRANT
53     5      405576483   2      KEY  (dd0038ef247f)   RangeS-U GRANT




Show quoteHide quote

Tod Nelson wrote:
Show quoteHide quoteYou need to use a separete query to get the next available ID and have
that ID driven from another table. Otherwise, you'll be forced to lock
the table while you determine the next id value and then perform the
insert - which is not going to provide good performance.

Just an example, but you can use a table of next key values:

NextKeyVal (TableName NVARCHAR(128) NOT NULL PRIMARY KEY, NextKeyVal INT
NOT NULL)

If you use a stored procedure, you could completely manage getting the
next key value using a single stored procedure which is called from
elsewhere. You may also be able to use a function which is called inline
from the insert statements (I don't have time right now to post sample
code). But I would try a scalar function that is passed the table name
and returns an INT and try using that function right in the insert.

The query that generates the next key could look something like:

Update dbo.NextKeyVal Set @NextID  = NextKeyVal  = (NextKeyVal + 1)

or see this post (mind the url line breaks):
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/270ba44cff85df9f/d295bb4966e76c10?lnk=st&q=%22Andrew+J.+Kelly%22+next+update&rnum=10&hl=en#d295bb4966e76c10

On Sat, 27 Aug 2005 20:26:54 +0300, Tod Nelson wrote:

Hi Tod,

Since nobody else did, I'll bite.

*WHY* don't you want to use the identity property? From your
description, you're trying to achieve the same effect, but in a
"do-it-yourself" way. You'll end up having all the disadvantages of
identity, but none of the benefits.

Best, Hugo

Most efficient way to update/insert order details?!
NON-DETERMINISTIC?
Time Difference Calculations
Can we restore database to a specific folder
SET OF INT