Insert Records - 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)

Insert Records

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-03-04 : 18:06:25

Hello,

I am trying to insert 200 records on a table that has two fields:
TagID and Name. TagID is a Unique Identifier and is not generated in the table. I created some code but is not working and I am a little bit confused:


declare	@i integer
select @i = 1

while @i <= 800
begin
	insert into Tags (TagID, [name]) 
	values(newid(), select ('Tag ' + right('000' + convert(varchar(3), @i), 3)))
	select	@i = @i + 1
end

Could someone tell me how to solve this?

Thanks,
Miguel

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-04 : 18:15:49

You almost had it!

declare @i integer
select @i = 1

while @i <= 800
begin
insert into Tags (TagID, [name])
values(newid(), select 'Tag ' + right('000' + convert(varchar(3), @i), 3))
select @i = @i + 1
end

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 18:25:35

[code]INSERT Tags
(
TagID,
Name
)
SELECT NEWID(),
'Tag ' + RIGHT('00' + CAST(Number AS VARCHAR(3)), 3)
FROM master..spt_values
WHERE Type = 'p'
AND Number BETWEEN 1 AND 800[/code]

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

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-03-04 : 18:34:37

Thank You Peso.
That worked!

Thanks,
Miguel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-05 : 03:46:20

or if number is big


INSERT	Tags
	(
		TagID,
		Name
	)
SELECT	NEWID(),
	'Tag ' + RIGHT('00' + CAST(Number AS VARCHAR(3)), 3)
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 800

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources