Member_2_1242703
asked on
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?
i.e.
Numbers
======
1
2
3
4
5
6
7
8
9
Also, I want to be able to start the records at any number and finish the records at any number.
i.e.
Numbers
======
1
2
3
4
5
6
7
8
9
Also, I want to be able to start the records at any number and finish the records at any number.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
ASKER
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.
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.
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
GO
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
GO
Alter Table Tom Drop Column Scratch -- leave only numbers
Create Table Tom (id int identity, Scratch char(1)) -- optional if you have another table already
GO
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
GO
Alter Table Tom Drop Column Scratch -- leave only numbers
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