SQL Server 2008 - A clever way to fill a column with rising integers

Question

This is a really silly question probably, I'm working on a database and what I want to do is create a table with an ID (auto increment) and another column: "Number" (I realize it sounds useless but bare with me here please), and I need to fill this "Number" column with values from 1 to 180, each time adding 1 to the previous.

What would be a clever "automatic" way of doing that?

Answer 1

If I understood you correctly ROW_NUMBER() should solve your problem.

Example:

select ROW_NUMBER() OVER(ORDER BY ID) from sysobjects

If you want 1 to 180 and then again 1 to 180:

select ((ROW_NUMBER() OVER(ORDER BY ID)) - 1) % 180 + 1 from sysobjects

Update:

update tablename
set number = 
  (select number 
  from
    (select
      id,
      ((ROW_NUMBER() OVER(ORDER BY ID)) - 1) % 180 + 1 number
    from tablename) u
  where u.id = tablename.id)

Answer 2

WITH n AS(SELECT 0 nUNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)
SELECT n1.n * 100 + n2.n * 10 + n3.n + 1
  FROM n n1 CROSS JOIN n n2 CROSS JOIN n n3
 WHERE n1.n * 100 + n2.n * 10 + n3.n + 1 <= 180

Answer 3

Create a table with the columns you want (ID,Number) and set ID to auto increment. Once your done, use a while to load up to 180.

Here is the CREATE TABLE

CREATE TABLE [dbo].[<YOUR TABLE>](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Number] [int] NULL
) ON [PRIMARY]

Here is the INSERT

INSERT INTO <YOUR TABLE> (Number) VALUES (1);

WHILE SCOPE_IDENTITY() < 180
BEGIN
    INSERT INTO <YOUR TABLE> (Number) VALUES (SCOPE_IDENTITY()+1);
END

Answer 4

Itzik Ben-Gan has a great row-number generation method which does not use tables but only constants and cross join. Very elegant in my eyes.

from http://www.projectdmx.com/tsql/tblnumbers.aspx#Recur

;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT n
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 180 ; -- or any number 

You can replace the 180 with any number. instead of the select you can use the generated sequence for your insert.

Answer 5

In transact SQL a simple update should give you want you want

create table table_x ( A char(1), B int NULL )


declare @i int
select @i = 1

update table_x
set B=@i, @x=@i+1
from table_x 

select * from table_x

Answer 6

Make the column you want to update 0 then you can simply:

DECLARE @id INT = 0
UPDATE tbl
   SET @id = id = (@id % 180 + 1)

To just to increment remove % 180.