Help with cursor and batch

Posted on 2011-04-26
Last Modified: 2012-05-11
OK, I'm a developer and my DBA wants a cursor..he wants me to use a cursor to process a 100 inserts at a time until the end of the file is reached.. Problem is I'm not really good at cursors so I need your help.. I have a temp table that gets loaded every two weeks.. There could be thousands of records in the temp table.. there is a column in the temp table that has HTML and a column that has a select with replacements.. Dont worry.. this is already constructed below.. I need to process through 100 records from my temp table called @listings into a temp table called.@ActionType_Modified and then @listings gets updated from .@ActionType_Modified .I need the code below to run for 100 records at a time from @listings until the last record in @listings is reached.. Seems pretty simple right? One little criteria.. Every time 100 records is pulled from @listings and inserted into ActionType_Modified it needs to be deleted from @listings so the same 100 records dont get pulled again... Basically it needs to batch out 100 records at a time, insert into another table for processing and then updated into @listings again.. Look at the code and it should make sense..Just make the code below run 100 times for each record in @listing
set @SQLStr=''
select @SQLStr = @SQLStr + (case when row_number() over (order by listingid )=1 then '' else ' union ' end )+ UserActionSQL
from   @listings

insert into @ActionType_Modified (ListingID,UserActionTypeHTML)
exec (@SQLStr)

update @listings
set    UserActionTypeHTML = atm.UserActionTypeHTML 
from  @listings atw
inner join @ActionType_Modified atm on atw.listingID = atm.ListingID

Open in new window

Question by:cheryl9063
    LVL 19

    Expert Comment


    1) It is not clear what is the SQL statement in  @SQLStr , can you paste here an example , because the code above will not execute  since  @SQLStr is equal to an empty string and you concatenate a string which is not a legal statement
    2) to process only 100 at a time you don't need a cursor , you can use select top 100 to get the records  and delete top 100 after processing it
    LVL 21

    Accepted Solution

    Create x Local Cursor for Select field 1, field 2 from TAble
    open x
    fetchnext x into @field 1, @field 2
    while (@@fetch_status=0)

    fetchnext x into @field 1, @field 2
    close x
    deallocate x
    LVL 1

    Author Comment

    elimesika, as I said I HAVE to crate a cursor and the @SQL code above is not empty.. What is important and NOT addressed by you or PatelAlpesh is the fact I have to do a cursor for 100 rows at a time from @listings.. Can anyone show me the syntax for this?
    LVL 1

    Author Comment

    by:cheryl9063 that SQL syntax? Dont I have to Declare a cursor?
    LVL 1

    Author Comment

    So I have constructed the cursor below(see code) but I need it to run for 100 records at atime from @listings.. How?
    DECLARE batchcursor CURSOR FOR
     SELECT listingID, ActionTypeID, UserActionSQL,UserActionTypeHTML FROM @listings
    	OPEN batchcursor
    	FETCH NEXT FROM cursorName
    	INTO @listingID, @ActionTypeID,@UserActionSQL,@UserActionTypeHTML
    SET @SQLStr=''
    SELECT @SQLStr = @SQLStr + (CASE WHEN ROW_NUMBER() OVER (ORDER BY listingid )=1 THEN '' ELSE ' union ' END )+ UserActionSQL
    FROM   @listings
    INSERT INTO @ActionType_Modified (ListingID,UserActionTypeHTML)
    EXEC (@SQLStr)
    UPDATE @listings
    SET    UserActionTypeHTML = atm.UserActionTypeHTML 
    FROM  @listings atw
    INNER JOIN @ActionType_Modified atm ON atw.listingID = atm.ListingID
      FETCH NEXT FROM batchcursor
       INTO @listingID, @ActionTypeID,@UserActionSQL,@UserActionTypeHTML

    Open in new window

    LVL 19

    Assisted Solution

    with a cursor , you will have to define a counter variable and advance its value in the loop.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now