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.

 

I always thought the only way to have a computed or derived column in SQL was to use some kind of a view in place, but I found out that you can actually create a computed column on a table and that you can even “persist” the data and have it indexed.

I’m only guessing that behind the scenes, SQL treats this somewhat like a trigger, like a before-trigger, where just prior to the transaction being posted, the calculations are applied and the column is populated, then the full transaction is applied.

Here’s the article where I found this information.

And here’s an example:

use database

 

create table t

( a int, b int);

 

--adding the computed column

alter table t add

x as case a 

            when -1 then

                  b

            else

                  a

            end persisted not null

--using "persisted" is required if you want to make column "not null".  I believe "persisted" is somewhat implied if computed column is derived from PK columns.

 

--just showing that you can create an index on the new column

create index z on t (x)

 

 

insert into t values (1, -1)

insert into t values (2, -1)

insert into t values (-1, 4)

insert into t values (-1, 3)

insert into t values (-1, -1)

 

select * from t

 

 

 

--you can see that the computed column worked.

a       b       x     

1       -1      1     

2       -1      2     

-1      4       4     

-1      3       3     

-1      -1      -1