Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: 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
    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First 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

  3. #3
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    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):
    Code:
    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:
    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

  4. #4
    Join Date
    Sep 2003
    Posts
    176
    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.

  5. #5
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    As mentioned, forget the cursor! They are for row-by-row processing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •