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.
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 12341234Until 5000I have tried to write my own but have become stuck:USE OccupancyCREATE TABLE Bookings2(Booking_Skey INT IDENTITY (1,1) not null,PitchType_Skey INT not null)DECLARE @PitchType_Skey INTSET @PitchType_Skey = 1WHILE (@PitchType_Skey <= 4)BEGININSERT INTO Bookings2(PitchType_Skey)SELECT @PitchType_SkeySET @PitchType_Skey = @PitchType_Skey + 1ENDThank 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 tableCREATE 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);-- insertINSERT INTO Bookings2(PitchType_Skey)SELECT (n-1)%4+1FROM #NumbersORDER BY n;-- get rid of numbers tableDROP 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. |
|
|
wafw1971
Yak Posting Veteran
75 Posts |
Posted - 2013-02-08 : 11:12:51
|
Thank you, its all sorted now. |
|
|
|
|
|