Iterate Through Rows in SQL Server 2008

Iterate through rows in SQL Server 2008

If you must iterate(*), use the construct designed to do it - the cursor. Much maligned, but if it most clearly expresses your intentions, I say use it:

DECLARE @ID int
DECLARE IDs CURSOR LOCAL FOR select ID from SAMPLE where Name = @NameParameter

OPEN IDs
FETCH NEXT FROM IDs into @ID
WHILE @@FETCH_STATUS = 0
BEGIN
    exec myproc @ID

    FETCH NEXT FROM IDs into @ID
END

CLOSE IDs
DEALLOCATE IDs

---

(*) This answer has received a few upvotes recently, but I feel I ought to incorporate my original comment here also, and add some general advice:

In SQL, you should generally seek a set-based solution. The entire language is oriented around set-based solutions, and (in turn) the optimizer is oriented around making set-based solutions work well. In further turn, the tools that we have available for tuning the optimizer is also set-oriented - e.g. applying indexes to tables.

There are a few situations where iteration is the best approach. These are few are far between, and may be likened to Jackson's rules on optimization - don't do it - and (for experts only) don't do it yet.

You're far better served to first try to formulate what you want in terms of the set of all rows to be affected - what is the overall change to be achieved? - and then try to formulate a query that encapsulates that goal. Only if the query produced by doing so is not performing adequately (or there's some other component that is unable to do anything other than deal with each row individually) should you consider iteration.

While loop in SQL Server 2008 iterating through a date-range and then INSERT

SQL is a set based language and loops should be a last resort. So the set based approach would be to first generate all the dates you require and insert them in one go, rather than looping and inserting one at a time. Aaron Bertrand has written a great series on generating a set or sequence without loops:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

Part 3 is specifically relevant as it deals with dates.

Assuming you don't have a Calendar table you can use the stacked CTE method to generate a list of dates between your start and end dates.

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
FROM N3;

I have skipped some detail on how this works as it is covered in the linked article, in essence it starts with a hard coded table of 10 rows, then joins this table with itself to get 100 rows (10 x 10) then joins this table of 100 rows to itself to get 10,000 rows (I stopped at this point but if you require further rows you can add further joins).

At each step the output is a single column called N with a value of 1 (to keep things simple). At the same time as defining how to generate 10,000 rows, I actually tell SQL Server to only generate the number needed by using TOP and the difference between your start and end date - TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1). This avoids unnecessary work. I had to add 1 to the difference to ensure both dates were included.

Using the ranking function ROW_NUMBER() I add an incremental number to each of the rows generated, then I add this incremental number to your start date to get the list of dates. Since ROW_NUMBER() begins at 1, I need to deduct 1 from this to ensure the start date is included.

Then it would just be a case of excluding dates that already exist using NOT EXISTS. I have enclosed the results of the above query in their own CTE called dates:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Dates AS
(   SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
    FROM N3
)
INSERT INTO MyTable ([TimeStamp])
SELECT  Date
FROM    Dates AS d
WHERE NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE d.Date = t.[TimeStamp])

Example on SQL Fiddle

---

If you were to create a calendar table (as described in the linked articles) then it may not be necessary to insert these extra rows, you could just generate your result set on the fly, something like:

SELECT  [Timestamp] = c.Date,
        t.[FruitType],
        t.[NumOffered],
        t.[NumTaken],
        t.[NumAbandoned],
        t.[NumSpoiled]
FROM    dbo.Calendar AS c
        LEFT JOIN dbo.MyTable AS t
            ON t.[Timestamp] = c.[Date]
WHERE   c.Date >= @StartDate
AND     c.Date < @EndDate;

---

ADDENDUM

To answer your actual question your loop would be written as follows:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM myTable WHERE myTable.Timestamp = @CurrentDate)
    BEGIN
        INSERT INTO MyTable ([Timestamp])
        VALUES (@CurrentDate);
    END

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); /*increment current date*/
END

Example on SQL Fiddle

I do not advocate this approach, just because something is only being done once does not mean that I should not demonstrate the correct way of doing it.

---

FURTHER EXPLANATION

Since the stacked CTE method may have over complicated the set based approach I will simplify it by using the undocumented system table master..spt_values. If you run:

SELECT Number
FROM master..spt_values
WHERE Type = 'P';

You will see that you get all the numbers from 0 -2047.

Now if you run:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P';

You get all the dates from your start date to 2047 days in the future. If you add a further where clause you can limit this to dates before your end date:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate;

Now you have all the dates you need in a single set based query you can eliminate the rows that already exist in your table using NOT EXISTS

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

Finally you can insert these dates into your table using INSERT

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

INSERT YourTable ([Timestamp])
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

Hopefully this goes some way to showing that the set based approach is not only much more efficient it is simpler too.

Is there a way to loop through a table variable in TSQL without using a cursor?

First of all you should be absolutely sure you need to iterate through each row — set based operations will perform faster in every case I can think of and will normally use simpler code.

Depending on your data it may be possible to loop using just SELECT statements as shown below:

Declare @Id int

While (Select Count(*) From ATable Where Processed = 0) > 0
Begin
    Select Top 1 @Id = Id From ATable Where Processed = 0

    --Do some processing here

    Update ATable Set Processed = 1 Where Id = @Id 

End

Another alternative is to use a temporary table:

Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

The option you should choose really depends on the structure and volume of your data.

Note: If you are using SQL Server you would be better served using:

WHILE EXISTS(SELECT * FROM #Temp)

Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).

SQL - Iterating through table records

In SQL SERVER 2000/05/08 you can use a Cursor as shown below.

However before you go down the cursor path you should first look into the problems associated with cursors in SQL Server.

DECLARE @id VARCHAR(10)

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [String] AS 'ID' 
    FROM [dbo].[ConvertStringToTable]('1,2,3,4')
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @id
    -- do your tasks here

    FETCH NEXT FROM myCursor INTO @id

END

CLOSE myCursor
DEALLOCATE myCursor

Iterate through rows and insert data in temp table - SQL Server 2008

Don't iterate but use set based approaches like ...

WITH Nums AS(
    SELECT DISTINCT Value = number 
    FROM master..[spt_values] 
)
SELECT Date = DATEADD(d, n.Value - 1, t.[Start Date])
FROM Nums n CROSS JOIN TableName t
WHERE n.Value BETWEEN 1 AND t.Duration

Demo

Note that this approach works only until 2,164. Another approach is to use a number-table. Read:

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

If you want to insert it into another table you can use it in this way:

WITH Nums AS(
    SELECT DISTINCT Value = number 
    FROM master..[spt_values] 
)
INSERT INTO #TempTable ([Date]) 
SELECT Date = DATEADD(d, n.Value - 1, t.[Start Date])
FROM Nums n CROSS JOIN TableName t
WHERE n.Value BETWEEN 1 AND t.Duration

Looping through SELECT result set in SQL

The usual way to handle obtaining the identity in a set based manner is through the OUTPUT clause:

INSERT INTO TableA (ColumnA, ColumnB)
OUTPUT inserted.Id, inserted.ColumnA, inserted.ColumnB
SELECT  ColumnA, ColumnB
FROM    MyTable;

The problem here is that what you would ideally like to do is this:

INSERT INTO TableA (ColumnA, ColumnB)
OUTPUT inserted.Id, MyTable.ColumnC, inserted.ColumnD 
    INTO TableB (AID, ColumnC, ColumnD)
SELECT  ColumnA, ColumnB
FROM    MyTable;

The problem is that you can't reference the source table in the OUTPUT, only the target. Fortunately there is a workaround for this using MERGE, since this allows you to use reference both the resident memory inserted table, and the source table in the output clause if you use MERGE on a condition that will never be true you can the output all the columns you need:

WITH x AS
(   SELECT  ColumnA, ColumnB, ColumnC, ColumnD
    FROM    MyTable
)
MERGE INTO TableA AS a
USING x
    ON 1 = 0 -- USE A CLAUSE THAT WILL NEVER BE TRUE
WHEN NOT MATCHED THEN 
    INSERT (ColumnA, ColumnB)
    VALUES (x.ColumnA, x.ColumnB)
OUTPUT inserted.ID, x.ColumnC, x.ColumnD INTO TableB (NewID, ColumnC, ColumnD);

The problem with this method is that SQL Server does not allow you to insert either side of a foreign key relationship, so if tableB.NewID references tableA.ID then the above will fail. To work around this you will need to output into a temporary table, then insert the temp table into TableB:

CREATE TABLE #Temp (AID INT, ColumnC INT, ColumnD INT);
WITH x AS
(   SELECT  ColumnA, ColumnB, ColumnC, ColumnD
    FROM    MyTable
)
MERGE INTO TableA AS a
USING x
    ON 1 = 0 -- USE A CLAUSE THAT WILL NEVER BE TRUE
WHEN NOT MATCHED THEN 
    INSERT (ColumnA, ColumnB)
    VALUES (x.ColumnA, x.ColumnB)
OUTPUT inserted.ID, x.ColumnC, x.ColumnD INTO #Temp (AID, ColumnC, ColumnD);

INSERT TableB (AID, ColumnC, ColumnD)
SELECT AID, ColumnC, ColumnD
FROM #Temp;

Example on SQL Fiddle

---

Related Topics