SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Repeating While Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/08/2013 :  10:55:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3636 Posts

Posted - 02/08/2013 :  11:04:05  Show Profile  Reply with Quote
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 - 02/08/2013 :  11:12:51  Show Profile  Reply with Quote
Thank you, its all sorted now.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000