Numbers - 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 2005 Forums

Transact-SQL (2005)

Numbers

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-25 : 14:45:17

Hello,

I have the following:

insert into dbo.Categories (CategoryID, [Name])
select
newid(),
'Category ' + right('000' + convert(varchar(3), n + 1), 3)
from @Numbers

Numbers is a table with 1000 numbers.
How can I create only 10 records in Categories?
I need to restrict the number of @Numbers used.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 14:48:59

insert into dbo.Categories (CategoryID, [Name])
select top 10
newid(),
'Category ' + right('000' + convert(varchar(3), n + 1), 3)
from @Numbers

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-25 : 14:55:18

What is the structure of the @Numbers table? If it is what I think it is you can add a where clause: WHERE n < 11

Or you could do something like:

SET ROWCOUNT 10
insert into dbo.Categories (CategoryID, [Name])
select
newid(), 
'Category ' + right('000' + convert(varchar(3), n + 1), 3)
from @Numbers
ORDER BY N

-- or

insert into dbo.Categories (CategoryID, [Name])
select TOP 10
newid(), 
'Category ' + right('000' + convert(varchar(3), n + 1), 3)
from @Numbers
ORDER BY n

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-25 : 15:15:46

Hi,

I am creating the Numbers table as follows:

declare @Numbers table
(
n int identity(1,1) not null primary key clustered
)
while coalesce(scope_identity(), 0) <= 4
begin
insert @Numbers default values
end

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 15:30:31

This produces 1000 numbers?

E 12°55'05.25"
N 56°04'39.16"

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-25 : 15:46:51

No! lol ...

... I copied with 4 because I am using only 4 so my dummy data insert does not take to much time ... I didn't see it.

But my objective is to insert many records.

Thanks,
Miguel

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-25 : 15:51:05

Use the function on this link to generate the numbers.

Number Table Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


-- Demo using the function to ruturn numbers 1 to 10
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,10)

-- Demo using the function to ruturn numbers 1 to 2000
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,2000)

-- Demo using the function to ruturn numbers -1500 to 2000
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(-1500,2000)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 02:03:26

or with row_number() function


Select number from 
(
	select row_number() over (order by s1.name) as number from 
	sysobjects s1 cross join sysobjects s2
) as t
where number between 1 and 100

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources