SQL Server: how to create a repeating column 1-12?

Question

In 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. d. for (12 * 460343 =) 5524116 lines. Any wisdom on how I can do this? Thanks!

Answer 1

Insert word 48, then select from yourself several times. You will get there very quickly. This is surprisingly faster than you might think.

If you create a table with an int autoinc column, then at the 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 the 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