I have the following stored procedu

[Stickarm]

I have the following stored procedure that when I run it i keep running out of locks. I can't figure out a way to batch it so it does a managable # of records at a time, I am dealing with about 3 million records in this table. There is no Key field.

CREATE PROCEDURE sp_editimportdata
AS

insert into [sales and use returns](
[main acct],[sub acct] ,[tax type] ,period ,source ,[trandateorig] ......[sign-d] ,[tax due], remitted )

select disc.col001,
disc.col002 , disc.col003 , disc.col004 , disc.col005 , disc.col006 , disc.col007 ......, disc.col046 ,
(convert(money,disc.col047)) , (convert(money,disc.col048))
from disc

Thanks,
Phil

 

[tlbroadbent]

Often it is helpful to escalate locking immediately to a table lock rather than allow SQL to obtain nuerous row or page locks. Add the following lines before your Insert statement. This should speed the process considerably and prevent the "out of locks" error.

Begin Transaction

Select count(*)
From [sales and use returns]
With (tablockx holdlock)
Where 1=2

Add a Commit Transaction after the Insert statement. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tlbroadbent]

Note: You can read more about Lock Escalation in faq183-767. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.