Inserting over a lakh of records

[rsunitha]

I have a table with a column userid. There are around 1 lakh userids.
Similarly, Iam having a table UserData where I have to insert for each userid.
The data is sent thru asp gae and it take around 45 minutes to complete and mostly the system hangs. Please help

--Sunitha





CREATE Procedure InsertMailstoUserData
@data1 nvarchar(256),
@data2 nvarchar(256),
@data3 nvarchar(256),
@data4 smallint,
@data5 ntext = null,
@data6 int


AS

set nocount on

Begin

DECLARE @userid
DECLARE @Count_1 DECIMAL
SET @Count_1=1

DECLARE userid CURSOR FOR SELECT userid FROM User

OPEN userid
-- there are over lakh of userid
FETCH NEXT FROM userid INTO @userid

WHILE @@FETCH_STATUS = 0
BEGIN



DECLARE @TransName varchar(30)
SET @TransName= 'NEWINSERT'
BEGIN TRANSACTION @TransName
-- for each userid insert is to be done
Insert into UserData(userid,data1,data2,data3,data4,data5,data 6) values (@userid,@data1,@data2,@data3,@data4,@data5,@data6 )

IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION @TransName
RAISERROR ('Cannot insert a record in userdata.', 16, 1)
return
END

COMMIT TRANSACTION @TransName

SET @Count_1 = @Count_1 + 1

FETCH NEXT FROM userid INTO @userid
END

CLOSE pin_cursor
DEALLOCATE pin_cursor


End
GO

[skhanal]

It seems you are trying to insert the same data for all users in User table, if that is correct, avoid cursor by using one INSERT statement.



CREATE Procedure InsertMailstoUserData
@data1 nvarchar(256),
@data2 nvarchar(256),
@data3 nvarchar(256),
@data4 smallint,
@data5 ntext = null,
@data6 int


AS

set nocount on

Begin
DECLARE @TransName varchar(30)
SET @TransName= 'NEWINSERT'
BEGIN TRANSACTION @TransName
-- for each userid insert is to be done
Insert into UserData(userid,data1,data2,data3,data4,data5,data
6)
select userid,@data1,@data2,@data3,@data4,@data5,@data6
from user


IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION @TransName
RAISERROR ('Cannot insert a record in userdata.', 16, 1)
return
END

COMMIT TRANSACTION @TransName
End

[rsunitha]

Basically Iam taking a userid from a table and then inserting ame data for that userid.