Help with Cursor to insert 100 rows at a time

Hi all,

Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.

Here is what I have till now:

CREATE procedure Insert100RowsAtaTime
AS
SET NOCOUNT ON

declare @.Col1 int
declare @.Col2 char(9)
DECLARE @.RETURNVALUE int
DECLARE @.ERRORMESSAGETXT varchar(510)
DECLARE @.ERRORNUM int
DECLARE @.LOCALROWCOUNT int

declare Insert_Cur cursor local fast_forward
FOR
SELECT top 100 Col1,Col2 from Table1
WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/
from Table2)

set @.RETURNVALUE = 0
set @.ERRORNUM = 0

BEGIN

open Insert_Cur
fetch NEXT from Insert_Cur into @.Col1, @.Col2
while (@.@.FETCH_STATUS = 0)
insert into Table2 (Col1,Col2) select @.Col1,@.Col2

SELECT @.ERRORNUM = @.@.ERROR, @.LOCALROWCOUNT = @.@.ROWCOUNT
IF @.ERRORNUM = 0
BEGIN
IF @.LOCALROWCOUNT >= 1
BEGIN
SELECT @.RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @.RETURNVALUE = 1
RAISERROR ('INSERT FAILS',16, 1)
END
END
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END

fetch NEXT from Insert_Cur into @.Col1, @.Col2
end

close Insert_Cur
deallocate Insert_Cur

RETURN @.RETURNVALUE
ENDFirst of all, I don't understand what you really want to do so I can't give you a usable or correct response. I can tell you almost certainly that a cursor is not the correct answer.

You have a PK. A cursor isn't needed and it will probably hurt you in terms of both complexity and performance.

Can you describe what you really want in terms of the real world? In business or end-user terms, not in geek speak.

There are definitely ways to do what you want. They are probably simple and fast. I don't know enough to help you yet, but if you describe what you are trying to do a bit better then I'd bet that someone here can help.

-PatP|||It seems to me your not inserting all rows with 100 rows at the time, you're inserting 100 rows one at the time. After row no 100, the cursor is finished and your procedure is done.

This should be more like what you descibe (albeit not the most efficient way, but at least it eliminates the cursor):
WHILE EXISTS (
SELECT 1
FROM Table1
WHERE Col1 NOT IN (SELECT Col1 FROM Table2)
)
BEGIN
INSERT table2 (Col1, Col2)
SELECT top 100 Col1,Col2
FROM Table1
WHERE Col1 NOT IN (SELECT Col1 FROM Table2)

-- Maybe do some error checking here
END

A question about your code:
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
What's this supposed to do?
1) The message is already raised the moment the error occurs
2) What about the placeholders in the messages?
3) @.@.ERROR at that moment is always 0|||Table 1 has over 500 million rows. The task is To select data from Table 1 (based on business rules) and insert into Table 2. The concern was Selecting all data may take a long time to execute and in case of any issue with the quety, a long time to roll back. Hence 100 rows at a time using a cursor.|||As mentioned, forget the cursor! They are for row-by-row processing.