• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Help with cursor and batch

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
DECLARE @SQLStr NVARCHAR(MAX)
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

0
cheryl9063
Asked:
cheryl9063
  • 3
  • 2
2 Solutions
 
elimesikaCommented:
HI
Questions

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
0
 
Alpesh PatelAssistant ConsultantCommented:
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)
begin


fetchnext x into @field 1, @field 2
end
close x
deallocate x
0
 
cheryl9063Author Commented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
cheryl9063Author Commented:
Patel..is that SQL syntax? Dont I have to Declare a cursor?
0
 
cheryl9063Author Commented:
So I have constructed the cursor below(see code) but I need it to run for 100 records at atime from @listings.. How?
DECLARE @SQLStr NVARCHAR(MAX)
DECLARE batchcursor CURSOR FOR
 SELECT listingID, ActionTypeID, UserActionSQL,UserActionTypeHTML FROM @listings
 
	OPEN batchcursor
	FETCH NEXT FROM cursorName
	INTO @listingID, @ActionTypeID,@UserActionSQL,@UserActionTypeHTML
	WHILE @@FETCH_STATUS = 0

BEGIN
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
 END

Open in new window

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now