Posted by Weyus on 06/27/07 19:27

All,

I need to do some bulk loading of data, and in order to do it, I need
to be able to do the following:

1) Retrieve the current identity value for the table using SELECT
IDENT_CURRENT('myTable')
2) Generate a data file with pre-created identity column values
3) SET INDENTITY_INSERT 'myTable'
4) Perform a BULK INSERT with my just-written data file.

Because I need to be able to rely on the identity values that I
generate in my file being valid, I need to be sure that no other
processes can be inserting into this table from the time I pull the
IDENT_CURRENT value out until I'm finished with doing my load.
Bascially, I have to be sure that the range of identity column values
that I'm generating will be valid when I go to do my BULK INSERT.

What I'd like to be able to do is put #1-4 within a transaction, and
set an exclusive lock on the table before I do the SELECT
IDENT_CURRENT('myTable') command. This lock will be released at the
end of the transaction.

What is the best way to set an exclusive table lock for this purpose?
It appears that the only way to set locks explicitly in SQL Server is
by using a special clause on a SELECT/INSERT/UPDATE/DELELE statement.
Is this correct?

Also, if I successfully set an exclusive lock at the beginning of this
transaction, is it guaranteed to persist until the end of the
transaction?

Thanks,
Wes

[Back to original message]