Populating a table with a sequence of numbers

I have a table with one column named Numbers. Is there a way to populate this column with a sequence of numbers, (each number being a new record) without physically entering each number as a record?



Also, I want to be able to start the records at any number and finish the records at any number.

Mike MillerSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yep, you make the Numbers column an identity field.

If you are creating the table in Enterprise manager, go to the column properties and set Identity to yes.

Then when you insert a record, the field automatically gets the next number in it.

Theres more information in SQL Books Online (ie how it handles holes in the range etc.)

Anothe solution is to use a UDF, which takes parameters of start and finish, and returns a (virtual) table. Then you can join any table to that UDF to get the number range you want.

Let me know if you want the UDF
Hi mwmiller78,

A simple T-SQL loop would also achieve what you are after, this can be run from query analyser and assumes your table is called myTable:

--start code
SET @iNumber = 10 --or whatever number you want to start at

TRUNCATE TABLE myTABLE -- this will clear all the current numbers in myTable

WHILE @iNumber <= 100 -- or whatever number you want to end at
      INSERT INTO myTable (Numbers) VALUES @iNumber
      SET @iNumber = @iNumber + 1
--end code

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike MillerSoftware EngineerAuthor Commented:
No. I dont want to number it as i insert a record. The column is the only column in the table. I want to fill it with numbers. Those numbers will be the records.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike MillerSoftware EngineerAuthor Commented:
sorry mcmonap. i guess you submitted yours before i submitted mine...i'm trying your query now
If you are interested, if you use a UDF, you don't even need to create a table.
Mike MillerSoftware EngineerAuthor Commented:
Is there not a function that can populate a table that is already in place?
Yes that what mcmonap supplied more or less.

what was talking about was something like this:

SELECT * FROM dbo.f_NumberList(10,100)

...will return a table with numbers from 10 to 100

You just need to code the f_NumberList function.
Brendt HessSenior DBACommented:
Much depends on how many numbers you want.  For example, this simple script creates a table with 2048 entries using 12 insert statements.

Create Table Tom (id int identity, Scratch char(1))  -- optional if you have another table already


Insert Into Tom (Scratch) Values ('x')
Insert Into Tom (Scratch) Values ('x')  -- Do this twice, so that the insert... select will do more than a simple INSERT would.

Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 2
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 4
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 8
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 16
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 32
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 64
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 128
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 256
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 512
Insert Into Tom (Scratch) Select Scratch From Tom  -- Add 1024


Alter Table Tom Drop Column Scratch  -- leave only numbers

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.