inserting GL001 to GL100 in a loop

Question

Post reply

inserting GL001 to GL100 in a loop

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
February 1, 2010 at 5:04 am

#137800

How to insert the values GL001 to GL100 to a table.
i can write the loop like
declare @id int
set @id = 001
While @id <=100
begin
insert myTable(Activity_Codes)
select 'GL'+convert(varchar, @id)
set @id=@id+1
end
but my concern is how to append an additional 00 to 1 so it'll be 001
and a 0 to 21 so that it'll be 021, etc....
Thanks,
KB
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 1

Try this

declare @id int

set @id = 001

While @id <=100

begin

insert myTable(Activity_Codes)

select 'GL'+ Right('000' + convert(varchar, @id),3)

set @id=@id+1

end

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 2

vstitte (2/1/2010)
Try this
declare @id int
set @id = 001
While @id <=100
begin
insert myTable(Activity_Codes)
select 'GL'+ Right('000' + convert(varchar, @id),3)
set @id=@id+1
end

Yes, got it...

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 3

vstitte

SSCarpal Tunnel

Points: 4475

February 1, 2010 at 5:12 am

#1111522

You are welcome.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Get out of the habit of looping , use tally tables

This ...

insert myTable(Activity_Codes)

select 'GL'+ Right('000' + convert(varchar(3), number),3) from master..spt_values where type = 'P' and number between 1 and 100

Is preferable

Clear Sky SQL
My Blog[/url]

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 5

Thanks Dave.

Awesome. I am going to start thinking Tally going forward. Really cool.

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 6

Dave Ballantyne (2/1/2010)
Get out of the habit of looping , use tally tables
This ...
insert myTable(Activity_Codes)
select 'GL'+ Right('000' + convert(varchar(3), number),3) from master..spt_values where type = 'P' and number between 1 and 100
Is preferable

Wow... !!!

Thanks,
Santhosh

Human Knowledge Belongs To The World !!