Loop. Is this possible? - 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)

Loop. Is this possible?

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-11-18 : 21:43:16

Hello,

I am inserting testing data in a table as follows:
insert into Tags (Text) select 'Tag 01'
insert into Tags (Text) select 'Tag 02'

I want to insert 200 records: "Tag 01" to "Tag 200"
Is it possible to do this using some kind of loop?

Thanks,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-18 : 21:54:08

[code]DECLARE @i integer
SELECT @i = 1

WHILE @i <= 200
BEGIN
INSERT INTO Tags ([text])
SELECT 'Tag ' + RIGHT('00' + CONVERT(varchar(2), @i), 2)
SELECT @i = @i + 1
END

OR

INSERT INTO @Tags ([text])
SELECT 'Tag ' + RIGHT('00' + CONVERT(varchar(2), NUMBER), 2)
FROM F_TABLE_NUMBER_RANGE(1, 200)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ranganath
Posting Yak Master

209 Posts

Posted - 2007-11-19 : 00:38:47

Try this

SELECT 'Tag ' + Cast(Number as varchar(100))
From Master..spt_values where Number < =200 and type = 'p'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 02:13:28

SELECT top 200 'Tag ' + Cast(row_number() over (order by name) as varchar(100))
From sysobjects

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2007-11-19 : 05:03:30

hi,

Try this also

SELECT 'Tag ' + right(1000+Number,3)AS 'Tag'
From Master..spt_values where Number < = 200 and type = 'p'

ranganath
Posting Yak Master

209 Posts

Posted - 2007-11-20 : 00:53:00

Select top 9 'Tag ' + '0' + Cast(row_number() over (order by name) as varchar(100)) as Tag
From sysobjects
union all
Select 'Tag ' + Cast(Number as varchar(100)) as Tag
From Master..spt_Values where Type = 'P' and Number >= 10 and Number <=200

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources