SQL Server: How can I create a column with the repeating sequence 1-12?

Question

Long story short, I would like to create a column that repeats the pattern 1,2,3,4,5,6,7,8,9,10,11,12,1,2,3,4,...etc. for (12 * 460343 =) 5524116 rows. Any wisdom on how I could complete this? Thank you!

Answer 1

Insert say 48 then select into from self several times. You will get there real fast. It is surprisingly faster than one would think.

If you create a table with an int autoinc column then at end:

delete from table where id>5524116

Edit here you go

    create table idFix
    (   id bigint auto_increment primary key,
        num int not null
    )engine=myisam;

    -- prime it    
    insert into idFix(num) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

    -- this is pretty fast, don't laugh
    -- run the following line 19 times
    insert into idFix(num) select num from idFix;

    -- you now have 6.2m rows (6,291,456)
    select count(*) from idFix

    delete from idFix where id>5524116;

    select count(*) from idFix;

    select min(num),max(num) from idFix;

Takes 3 minutes max

Use your helper table then for the love of Pete drop it !

Answer 2

Use a loop and do some mod division with your counter.

DECLARE @LoopCounter bigint
SET @LoopCounter = 0

CREATE TABLE #YourValues
(
    YourValue_Key int NOT NULL identity (1,1) PRIMARY KEY, 
    YourValue_OneThrough12Repating int
)

WHILE @LoopCounter < 5524116
    BEGIN
        INSERT INTO #YourValues (YourValue_OneThrough12Repating) VALUES ((@LoopCounter % 12) + 1)
        SET @LoopCounter = @LoopCounter + 1
    END

SELECT * FROM #YourValues

DROP TABLE #YourValues