Rick Caminiti dotCOM

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.