?
Solved

Populating a table with a sequence of numbers

Posted on 2005-03-31
8
Medium Priority
?
412 Views
Last Modified: 2012-05-05
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
Comment
Question by:Mike Miller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13675849
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
 
LVL 15

Accepted Solution

by:
mcmonap earned 1000 total points
ID: 13676070
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
 

Author Comment

by:Mike Miller
ID: 13676076
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Mike Miller
ID: 13676094
sorry mcmonap. i guess you submitted yours before i submitted mine...i'm trying your query now
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13676264
If you are interested, if you use a UDF, you don't even need to create a table.
0
 

Author Comment

by:Mike Miller
ID: 13676284
Is there not a function that can populate a table that is already in place?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13676325
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
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 13677189
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question