Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Repeating While Statement

Author  Topic 

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-08 : 10:55:50
I need help on a While Statement, I have never used one before and was wondering had to repaeat the sequence until I hit a target:

For Example
1
2
3
4
1
2
3
4
Until 5000

I have tried to write my own but have become stuck:
USE Occupancy
CREATE TABLE Bookings2
(Booking_Skey INT IDENTITY (1,1) not null,
PitchType_Skey INT not null)

DECLARE @PitchType_Skey INT
SET @PitchType_Skey = 1
WHILE (@PitchType_Skey <= 4)

BEGIN
INSERT INTO Bookings2(PitchType_Skey)
SELECT @PitchType_Skey
SET @PitchType_Skey = @PitchType_Skey + 1
END

Thank you for your help in advance.

Wayne

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 11:04:05
It is easiest if you have a numbers table. If you don't have one, create one like shown below and then use it like shown in the second query.
-- create numbers table
CREATE TABLE #Numbers(n INT NOT NULL PRIMARY KEY);
;WITH cte(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 5000)
INSERT INTO #Numbers SELECT n FROM cte OPTION (MAXRECURSION 0);

-- insert
INSERT INTO Bookings2(PitchType_Skey)
SELECT
(n-1)%4+1
FROM
#Numbers
ORDER BY n;

-- get rid of numbers table
DROP table #Numbers
If you don't have permissions to create temp tables, you could use a cte approach - if that is the case, please reply back.
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-08 : 11:12:51
Thank you, its all sorted now.
Go to Top of Page
   

- Advertisement -