How to reliably ensure that only one set of rows is bulk imported into a table at a time

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

Re: How to reliably ensure that only one set of rows is bulk imported into a table at a time

It looks like if I do a

SELECT blah FROM TABLE WITH (TABLOCK XLOCK)

at the very beginning of my transaction, that would do the trick
right?

Wes

Re: How to reliably ensure that only one set of rows is bulk imported into a table at a time

On Wed, 27 Jun 2007 19:40:12 -0000, Weyus wrote:

>It looks like if I do a
>
>SELECT blah FROM TABLE WITH (TABLOCK XLOCK)
>
>at the very beginning of my transaction, that would do the trick
>right?
>
>Wes

If you plan on locking the entire table during the load, why not just
let the IDENTITY value be assigned normally? It seems like you are
creating a load of extra work for yourself.

Roy Harvey
Beacon Falls, CT

Re: How to reliably ensure that only one set of rows is bulk imported into a table at a time

On Jun 27, 2:57 pm, Roy Harvey wrote:
> On Wed, 27 Jun 2007 19:40:12 -0000, Weyus wrote:
> >It looks like if I do a
>
> >SELECT blah FROM TABLE WITH (TABLOCK XLOCK)
>
> >at the very beginning of my transaction, that would do the trick
> >right?
>
> >Wes
>
> If you plan on locking the entire table during the load, why not just
> let the IDENTITY value be assigned normally? It seems like you are
> creating a load of extra work for yourself.
>
> Roy Harvey
> Beacon Falls, CT

Because I have two tables, A and B, both of which I need to bulk load.
B has a foreign key to A. A has constraints on it. B has no
constraints.
So I have two files, one for loading A and one for loading B.

If I bulk load A and some of the constraints kick out records, such
that not all of the records load, then when I load B, I will not have
a way to know how to set the foreign key in B correctly.

If I was just loading one table, your point would be totally valid.

Thanks,
Wes

Re: How to reliably ensure that only one set of rows is bulk imported into a table at a time

On Jun 27, 3:38 pm, Weyus wrote:
> On Jun 27, 2:57 pm, Roy Harvey wrote:
>
>
>
> > On Wed, 27 Jun 2007 19:40:12 -0000, Weyus wrote:
> > >It looks like if I do a
>
> > >SELECT blah FROM TABLE WITH (TABLOCK XLOCK)
>
> > >at the very beginning of my transaction, that would do the trick
> > >right?
>
> > >Wes
>
> > If you plan on locking the entire table during the load, why not just
> > let the IDENTITY value be assigned normally? It seems like you are
> > creating a load of extra work for yourself.
>
> > Roy Harvey
> > Beacon Falls, CT
>
> Because I have two tables, A and B, both of which I need to bulk load.
> B has a foreign key to A. A has constraints on it. B has no
> constraints.
> So I have two files, one for loading A and one for loading B.
>
> If I bulk load A and some of the constraints kick out records, such
> that not all of the records load, then when I load B, I will not have
> a way to know how to set the foreign key in B correctly.
>
> If I was just loading one table, your point would be totally valid.
>
> Thanks,
> Wes

This is no longer an issue. Thanks for the help.

Wes

Re: How to reliably ensure that only one set of rows is bulk imported into a table at a time

Weyus (weyus@att.net) writes:
> Because I have two tables, A and B, both of which I need to bulk load.
> B has a foreign key to A. A has constraints on it. B has no
> constraints.
> So I have two files, one for loading A and one for loading B.
>
> If I bulk load A and some of the constraints kick out records, such
> that not all of the records load, then when I load B, I will not have
> a way to know how to set the foreign key in B correctly.
>
> If I was just loading one table, your point would be totally valid.

I can't escape to note that this would be a lot simpler if you did
not use IDENTITY at all.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx