while syntax - 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)

while syntax

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-07-23 : 01:35:02

is possible to insert new record using 'while' syntax and the values :
00000
00001
00002
.
.
.
zzzzz

oh

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-23 : 02:43:02

declare @i int
set @i=0
while @i<=10000
begin
insert into t select right('00000'+cast(@i as varchar(5)),5)
set @i=@i+1
end

- Vit

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-07-23 : 03:34:16

thx vit, your script is running well
but, is possible to generate value from
AAAAA0 to zzzzz( char)?

oh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-23 : 11:12:31

quote:

00000
00001
00002
.
.
.
zzzzz

FYI -- what you are asking for is:

36 ^ 5 = 60,466,176 rows in your table.

Are you sure this is what you want?

- Jeff

Edited by - jsmith8858 on 07/23/2003 11:41:38

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-23 : 11:42:51

If you DO want to do this, consider just converting identities or numbers into the format you need.

something like:



declare @i int;
declare @C VARCHAR(36);

set @i = 36  -- change this number as needed

set @c = '0123456789abcdefghijklmnopqrstuvwxyz'

select substring(@c, (@i / power(36,4)) % 36 + 1,1) + 
	substring(@c,(@i / power(36,3)) % 36 + 1,1) +
	substring(@c,(@i / power(36,2)) % 36 + 1,1) +
	substring(@c,(@i / power(36,1)) % 36 + 1,1) +
	substring(@c,(@i / power(36,0)) % 36 + 1,1)

should get you started ....

- Jeff

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-07-23 : 23:29:54

thx jeff i need it to analizing ID in my table are used, cause The ID are not auto number and the id are character combine with number

oh

joseph
Starting Member

10 Posts

Posted - 2003-07-24 : 00:14:02

declare @Letter table(i char(1))

insert into @Letter(i)
select '0' as i
union all select '1' as i
union all select '2' as i
union all select '3' as i
union all select '4' as i
union all select '5' as i
union all select '6' as i
union all select '7' as i
union all select '8' as i
union all select '9' as i
union all select 'B' as i
union all select 'C' as i
union all select 'D' as i
union all select 'E' as i
union all select 'F' as i
union all select 'G' as i
union all select 'H' as i
union all select 'I' as i
union all select 'J' as i
union all select 'K' as i
union all select 'L' as i
union all select 'M' as i
union all select 'N' as i
union all select 'O' as i
union all select 'P' as i
union all select 'Q' as i
union all select 'R' as i
union all select 'S' as i
union all select 'T' as i
union all select 'U' as i
union all select 'V' as i
union all select 'W' as i
union all select 'X' as i
union all select 'Y' as i
union all select 'Z' as i

select a.i + b.i+c.i +d.i +F.I
from
@Letter as a cross join @Letter as b
cross join @Letter as c cross join @Letter as d cross join @Letter as F

--A NEW MR CROSS JOIN!

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 12:27:35

I imagine that'll generate some X-rated words...

Brett

8-)

joseph
Starting Member

10 Posts

Posted - 2003-07-25 : 23:21:16

quote:

I imagine that'll generate some X-rated words...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources