Locking a table during insertion

I've a table that expects 5000 record to be inserted as a bulk insert but I've to select the max id from the table for each statement I've decided to write a stored procedure that selects the max ID once and increment it for each re开发者_如何学Gocord to quickly insert records but it is possible to have some other users use the same stored to insert whick could make a conflict How can I lock the table exclusively until I finish inserting by using a Sp or a normal insert script?

Like Quassnoi said, your best bet is to use IDENTITY, and have a much less convoluted and complicated process.

If you are committed to using the process you described, all it should take is:

INSERT INTO MyTable WITH (TABLOCK)
(field 1, field2, field3...)
VALUES
(value 1, value 2, value 3...)

The TABLOCK hint puts an exclusive table lock for an insert/update (you can also use TABLOCKX which is an explicit exclusive lock but it's not necessary except for a SELECT).

The TABLOCK hint will also enable minimally logged inserts if your trace flags are set up correctly, which are ordinarily significantly faster, though with only 5000 records you may not notice a difference.