Insert numbers 500 to 1000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert numbers 500 to 1000

Author

Topic

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-04-10 : 11:53:18

I am going to be inserting 500 records and want to apply the numbers 500 to 1000 to the records. How do I do this?

Is there a specific function I can use?

For example:

500, FirstName, LastName, Email
501, FirstName, LastName, Email
502, FirstName, LastName, Email
503, FirstName, LastName, Email
504, FirstName, LastName, Email

Thanks!
James

James Hunt

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-10 : 12:40:08

this is cool : [url]http://www.sqlteam.com/item.asp?ItemID=765[/url]
a number table is very very handy : [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 (fancy implementation)[/url]

I suggest that you just create a number table, and use that.
Here is one way...

create table number(n int primary key)
go

insert number select 1
while @@rowcount > 0
	insert	number(n)
	select	n+(select max(n) from number)
	from	number
	where	(n+(select max(n) from number)) < 8001
go

select n,'mydata' as somecolumn from number where n between 500 and 1000

select n from number

rockmoose

pootle_flump

1064 Posts

Posted - 2006-04-10 : 12:49:43

Here's another one. Once you get your head round how it works you will know you are on your way to understanding SQL

http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-10 : 12:58:17

quote:
Update, December 1, 2005: Fixed the insert script for SQL Server 2005; master..spt_values now has a lot more numbers (0-2048 instead of 0-255) so as-is the script was failing. It should work properly now.

Gentle reminder not to rely too much on system tables

But then agains a 4194304 row number table might be handy

, why is it taking so long in sql2005?

rockmoose

pootle_flump

1064 Posts

Posted - 2006-04-10 : 13:10:09

Lol - yes - hadn't noticed that. As far as pure SQL goes it is just such a terribly elegant way of generating a load of numbers I couldn't help posting.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-10 : 13:23:14

quote:
Originally posted by pootle_flump

Lol - yes - hadn't noticed that. As far as pure SQL goes it is just such a terribly elegant way of generating a load of numbers I couldn't help posting.

elegant or brute-force?

select top 8000 identity(int,1,1) as n into number from syscolumns cross join syscolumns t
alter table number add constraint PK_number primary key(n)

select * from number
--drop table number

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-04-10 : 23:06:45

How do I pull the data from the datatable and put the numbers with it?

How do I put a table of firstnames and lastnames with the table of numbers?

James Hunt

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-10 : 23:25:09

quote:
Originally posted by pootle_flump

Here's another one. Once you get your head round how it works you will know you are on your way to understanding SQL
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx

You call that a number table? This is a number table:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

And when you can explain how it works, you really do understand SQL, or at least cross joins. Don't get me started on elegant either...

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 00:00:14

quote:
Originally posted by jimmy.hunt

I am going to be inserting 500 records and want to apply the numbers 500 to 1000 to the records. How do I do this?

quote:
Originally posted by jimmy.hunt

How do I pull the data from the datatable and put the numbers with it?

How do I put a table of firstnames and lastnames with the table of numbers?

James Hunt

Are you inserting the data from one table to another table ?

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-04-11 : 00:14:28

Yes. I'm inserting a table from one table to another.

Thanks!

James Hunt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 00:19:51

You can use a temp table with identity field to generate the required numbering.
example :


create table #temp
(
    num        int identity(500, 1)
    firstname  varchar(50),
    lastname   varchar(50),
    email      varchar(50)
)

insert into #temp (firstname, lastname, email)
select firstname, lastname, email
from   your_source_table

insert into your_target_table(your_number_col, firstname, lastname, email)
select num, firstname, lastname, email
from   #temp

rmason
Starting Member

12 Posts

Posted - 2006-04-11 : 08:09:12

Here's a very compact way to create a list of numbers that doesn't rely on sys table etc and is very quick. Just increase or decrease the number of coss join depending on how many numbers you want... this example will give you 0 to 999,999.

Declare @tmpN Table
(
Number Varchar(10)
)

Insert @tmpN
Select 0 Union
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 Cast((n1.Number + n2.Number + n3.Number + n4.Number + n5.Number + n6.Number) As Int)
From @tmpN n1
Cross Join @tmpN n2
Cross Join @tmpN n3
Cross Join @tmpN n4
Cross Join @tmpN n5
Cross Join @tmpN n6
Order By
Cast((n1.Number + n2.Number + n3.Number + n4.Number + n5.Number + n6.Number) As Int)

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-04-19 : 23:48:13

I have about a thousand records I'm dealing with and I'm dealing with varchar(8000) data. So neither of these solutions work. Any other ideas?

Thanks!
James

James Hunt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-20 : 00:01:12

quote:
Originally posted by jimmy.hunt

I have about a thousand records I'm dealing with and I'm dealing with varchar(8000) data. So neither of these solutions work. Any other ideas?

Thanks!
James

James Hunt

What is the issue with varchar(8000) that none of the solutions is workable for you ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-20 : 01:58:12

Where do you want to show the data?
Cant you number the results at Front End application if any?

Madhivanan

Failing to plan is Planning to fail

mr_mist
Grunnio

1870 Posts

Posted - 2006-04-20 : 03:53:54

Why not use a seeded identity value ?

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources