-
Inserting over a lakh of records
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
-
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
-
Basically Iam taking a userid from a table and then inserting ame data for that userid.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|