Using a T-SQL Cursor
I needed to loop through a large table by date, one month at a time and process the data. This is the basic cursor that I wrote in T-SQL. It’s pretty straight-forward.
First, you declare the cursor as a select statement.
Then you open the cursor (make sure you CLOSE it and DEALLOCATE it when you’re done!!)
Declare cursor-scope variables to fetch the data into and fetch the data from the cursor.
Loop through the data while the fetch status is “SUCCESS”.
Then check to make sure the fetch status indicates that the row was loaded properly and do whatever processing you need to do.
When done processing, fetch the next row and continue looping.
After the looping, close the cursor and deallocate it from memory.
DECLARE cur CURSOR
FOR select distinct cast(convert(varchar(25),dateadd(dd,-(day([date_column])-1),[date_column]),101) as datetime) begdate
,cast(convert(varchar(25),dateadd(dd,-(day(dateadd(mm,1,[date_column]))-1),dateadd(mm,1,[date_column])),101) as datetime) enddate
from [dbo].[StagingTable]
order by 1;
OPEN cur
DECLARE @begdate datetime, @enddate datetime;
FETCH NEXT from cur INTO @begdate, @enddate
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
INSERT INTO [dbo].[DestTable]
([col1]
,[col2]
,[col3]
,[col4]
,[col5])
SELECT [col1]
,[col2]
,[col3]
,[col4]
,[col5]
FROM [dbo].[StagingTable]
WHERE [date_column] >= @begdate
AND [date_column] < @enddate;
/*SELECT @begdate begdate, @enddate enddate, @@ROWCOUNT insrows;*/
DELETE
FROM [dbo].[StagingTable]
WHERE [date_column] >= @begdate
AND [date_column] < @enddate;
/*SELECT @begdate begdate, @enddate enddate, @@ROWCOUNT delrows;*/
END
FETCH NEXT from cur INTO @begdate, @enddate
END
CLOSE cur
DEALLOCATE cur
GO
|
Return value |
Description |
|
0 |
The FETCH statement was successful. |
|
-1 |
The FETCH statement failed or the row was beyond the result set. |
|
-2 |
The row fetched is missing. |