How to reliably ensure that only one set of rows is bulk imported into a table at a time
- From: Weyus <weyus@xxxxxxx>
- Date: Wed, 27 Jun 2007 19:27:40 -0000
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
.
- Follow-Ups:
- Prev by Date: Re: How to make a SQL run longer?
- Next by Date: Re: How to reliably ensure that only one set of rows is bulk imported into a table at a time
- Previous by thread: How to make a SQL run longer?
- Next by thread: Re: How to reliably ensure that only one set of rows is bulk imported into a table at a time
- Index(es):
Relevant Pages
|
