Time out Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Time out Error

Author

Topic

storm1981
Starting Member

9 Posts

Posted - 2006-05-21 : 06:48:17

I want to insert 100000 record in sql server using this stored Procedure ,but (i have error time out )

CREATE PROCEDURE dbo.[insertloop]
(@str varchar(50) ,
@num bigint , -- number of record
@recordnum bigint,
@counter bigint OUTPUT
)
AS
DECLARE @i bigint

set @i=0

WHILE @i < @recordnum
BEGIN

insert into numtable values(@str,@num)
set @i=@i+1

END
set @counter=@i
GO

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-21 : 07:06:22

Well dont use while loop for inserting the records..
here is the wonderful function written by MVJ.

I tested over at my end, your while loop took around 38 Sec for inserting 100000 records where as using the function it only take 13 sec.

link for the function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=Numbers,table

try this



Create PROCEDURE dbo.[insertloop]
(@str varchar(50) ,
@num bigint , -- number of record
@recordnum bigint,
@counter bigint OUTPUT
)
AS
insert into NumTable 
select @Str,@num from
(Select * From  dbo.F_TABLE_NUMBER_RANGE(1,@recordnum)) As F

Select @counter =Max(Number) From NumTable

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources