Is it possible to reserve a number of identity values before inserting into
a table in a multiuser environment ?

Not in a good way.  You could insert some rows and delete them, but that
would be really wierd.  Identity values shouldn't be used as meaningful
values that you care about the value of.

They are just good surrogates for keys that are optimized for multiple
people to insert and not get duplicates.

Show quoteHide quote

The issue is I want to avoid using a cursor in a trigger that inserts in 2
dependent tables:

declare cursor
.....

WHILE @@FETCH_STATUS = 0
BEGIN

insert audit
...

get @@identity

insert audit_detail
....

FETCH NEXT FROM ...

END

The fact that I need the identity value before I insert into the
AUDIT_DETAIL table is the reason for using the cursor.
Is there any other way around it ?

P.S.: the purpose is to use set theory to speed up the trigger


Show quoteHide quote

The best way to do this is to use the natural key to get the identity rows.
You know the rows you created and you should be able to associate them with
the audit rows some how.  I don't know your data:

I often use a guid as a batch key, like:

insert into audit (key value from table, batchGuid)

insert into audit_detail
select audit.identity
from audit
where key_values from audit (key of table from inserted, batch guid)

It can be a bit tricky, but it is much better than using a trigger.  If this
doesn't make sense I will see if I can find some code I have written in the
past to do this.

Show quoteHide quote

That's not how identity works.  You shouldn't need to reserve the values
before hand.

Here's what I do when I need to insert more than one row into more than one
related table.

Assuming you have the following tables:
CREATE TABLE parent
(
pk int identity(1,1) NOT NULL
  PRIMARY KEY CLUSTERED,
nk int not null
  UNIQUE NONCLUSTERED
)
CREATE TABLE child
(
pk int identity(1, 1) NOT NULL
  PRIMARY KEY CLUSTERED,
fk int NOT NULL REFERENCES parent(pk),
ck int NOT NULL,
UNIQUE NONCLUSTERED (fk, ck)
)


create these temp tables from the client app:

CREATE TABLE #parent (pk int NOT NULL, nk int not null)
CREATE TABLE #child (pk int NOT NULL, fk INT NOT NULL, ck int not null)

then insert these rows from the client app:
INSERT #parent (pk, nk) VALUES (1, 3)
INSERT #child (pk, fk, ck) VALUES (1, 1, 9)
INSERT #child (pk, fk, ck) VALUES (2, 1, 7)
INSERT #child (pk, fk, ck) VALUES (3, 1, 3)
INSERT #parent (pk, nk) VALUES (2, 6)
INSERT #child (pk, fk, ck) VALUES (4, 2, 11)
INSERT #child (pk, fk, ck) VALUES (5, 2, 1)
INSERT #parent (pk, nk) VALUES (3, 1)
INSERT #child (pk, fk, ck) VALUES (6, 3, 4)
INSERT #child (pk, fk, ck) VALUES (7, 3, 3)
INSERT #child (pk, fk, ck) VALUES (8, 3, 2)
INSERT #parent (pk, nk) VALUES (4, 8)
INSERT #child (pk, fk, ck) VALUES (9, 4, 5)

Then execute this procedure from the client app:

CREATE PROCEDURE InsertBoth AS
BEGIN
DECLARE @_ERROR INT

BEGIN TRANSACTION

INSERT parent (nk)
  SELECT nk
   FROM #parent
SELECT @_ERROR = @@ERROR
IF @_ERROR != 0 GOTO ERROR

UPDATE #child
  SET fk = parent.pk
     FROM parent
   JOIN #parent
    ON (#parent.nk = parent.nk)
     WHERE #child.fk = #parent.pk
SELECT @_ERROR = @@ERROR
IF @_ERROR != 0 GOTO ERROR

UPDATE #parent
  SET pk = parent.pk
  FROM parent
  WHERE #parent.nk = parent.nk
SELECT @_ERROR = @@ERROR
IF @_ERROR != 0 GOTO ERROR

INSERT child (fk, ck)
  SELECT fk, ck
   FROM #child
SELECT @_ERROR = @@ERROR
IF @_ERROR != 0 GOTO ERROR

UPDATE #child
  SET pk = child.pk
  FROM child
  WHERE #child.fk = child.fk
   AND #child.ck = child.ck
SELECT @_ERROR = @@ERROR
IF @_ERROR != 0 GOTO ERROR

COMMIT TRANSACTION
RETURN 0

ERROR:
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN -1
END
GO

finally read the new identity values from the temp tables into the client
app.

if you need more control, you're going to have to roll your own surrogate
generator.  It can get tricky, however, because of the locks.  I've seen
surrogate generators that included dummy columns in the surrogate table so
that the table would have one row per page, but that was on 6.5 when row
level locking didn't exist.


Show quoteHide quote

Hi
As others have aleready mentioned there is no a 'proper' way to do that
CREATE TABLE it
(
col INT NOT NULL IDENTITY(1,1)
)
INSERT it DEFAULT VALUES
GO
DBCC CHECKIDENT (it)
GO
INSERT it DEFAULT VALUES

DROP TABLE it

As well you may want to take a look at INSTEAD OF TRIGGER command in the
BOL.


Show quoteHide quote

Number of records for Month X, created between 7AM and Noon
sorting text numerically
SQL PUZZLE
CONVERT Update query problem
Determining Fiscal Quarter