Loops in SQL to create table

Question

I am trying to create a table that looks like the following, I was wondering if there was a quick way to create it rather than use a ton of unions.

 | Week_No | Day_Of_Week |
 |    1    |       1     |
 |    1    |       2     |
 |    1    |       3     |
 |    1    |       4     |
 |    1    |       5     |
 |    2    |       1     |
 |    2    |       2     |
 |    2    |       3     |
 |    2    |       4     |
 |    2    |       5     |

I need to create it so the week_no goes up to 53, each week has to have 1-5.

I am using Sql Server.

Regards,

Neil

Nope, never needed to use it. In C# I would of wrote it with a loop with a nested loop but in SQL I havent a clue. — Neil Norris, Jul 11 '17 at 13:44
creaate table, then while loop increments/reset days and increments weeks up to 53. Add insert statement inside the loop. — Pawel Czapski, Jul 11 '17 at 13:46
Possible duplicate of [Do while loop in SQL Server 2008](https://stackoverflow.com/questions/4487546/do-while-loop-in-sql-server-2008) — Tab Alleman, Jul 11 '17 at 13:48

score 2 · Answer 1 · answered Jul 11 '17 at 13:46

select Week_No,Day_Of_Week 
from 
( select top(53) row_number() over( order by (select null)) Week_No
  from sys.all_objects) w
cross join
( select top(5) row_number() over( order by (select null)) Day_Of_Week
  from sys.all_objects) d
order by Week_No,Day_Of_Week

score 2 · Answer 2 · answered Jul 11 '17 at 13:48

Loops should be avoided when possible. If you don't have a numbers/tally table, you can use an ad-hoc tally table.

;with cte0(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
      cteN(N) As (Select Row_Number() over (Order By (Select NULL)) From cte0 N1, cte0 N2)
Select Week_No=A.N
      ,Day_Of_Week = B.N
 From cteN A
 Cross Apply cteN B
 Where A.N<=53 and B.N<=5

score 1 · Answer 3 · answered Jul 11 '17 at 13:44

1

You will have to use CREATE TABLE syntax but you can try like below

create table tbl1( Week_No int, Day_Of_Week int);
insert into tbl1( Week_No , Day_Of_Week ) values (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),....

answered Jul 11 '17 at 13:44

Rahul

76,197
13
71
125

score 1 · Answer 4 · answered Jul 11 '17 at 13:45

First, create two table variables - and store in them 1-5, and 1-53 (for days and weeks). Then cartesian join the two and you're done! Change the last SELECT statement into an insert into your destination table and you're done.

DECLARE @WeekNumbers TABLE (WeekNo int)
DECLARE @DaysOfWeek TABLE (DayOfWeek int)

DECLARE @Count INT = 1

WHILE @Count <= 53
    BEGIN
        INSERT INTO @WeekNumbers
        VALUES
        (@Count)

        SET @Count = @Count + 1
    END

SET @Count = 1

WHILE @Count <= 5
    BEGIN
        INSERT INTO @DaysOfWeek
        VALUES
        (@Count)

        SET @Count = @Count + 1
    END

SELECT * 
FROM @WeekNumbers, @DaysOfWeek

score 1 · Accepted Answer · answered Jul 11 '17 at 13:46

1

Recursive Common table expressions:

WITH CTE_Weeks AS 
(
    SELECT 1 AS Week_No 
    UNION ALL 
    SELECT Week_No + 1
    FROM CTE_Weeks
    WHERE Week_No < 53
)
, CTE_Days AS 
(
    SELECT 1 AS Day_Of_Week  
    UNION ALL 
    SELECT Day_Of_Week  + 1
    FROM CTE_Days
    WHERE Day_Of_Week < 5
)
SELECT Week_No, Day_Of_Week
FROM CTE_Weeks
CROSS JOIN CTE_Days
ORDER BY Week_No, Day_Of_Week

answered Jul 11 '17 at 13:46

Nenad Zivkovic

18,221
6
42
55

This is perfect! You are the best Nenad! – Neil Norris Jul 11 '17 at 13:48

score 1 · Answer 6 · answered Jul 11 '17 at 13:50

1

declare @days_of_week int
declare @weekNbr int

set @weekNbr=1

while @weekNbr<=53

begin

set @days_of_week=1

while @days_of_week<=5
begin

 select @weekNbr,@days_of_week

set @days_of_week=@days_of_week+1
end
set @weekNbr=@weekNbr+1
end

answered Jul 11 '17 at 13:50

Kapil

987
5
11

Loops in SQL to create table

6 Answers6