Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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.

0
Mike Miller
Asked:
Mike Miller
1 Solution
 
nmcdermaidCommented:
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
0
 
mcmonapCommented:
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
DECLARE @iNumber INTEGER
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
BEGIN
      INSERT INTO myTable (Numbers) VALUES @iNumber
      SET @iNumber = @iNumber + 1
END
--end code
0
 
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mike MillerSoftware EngineerAuthor Commented:
sorry mcmonap. i guess you submitted yours before i submitted mine...i'm trying your query now
0
 
nmcdermaidCommented:
If you are interested, if you use a UDF, you don't even need to create a table.
0
 
Mike MillerSoftware EngineerAuthor Commented:
Is there not a function that can populate a table that is already in place?
0
 
nmcdermaidCommented:
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.
0
 
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

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

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now