Select Output with while loop - 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)

Select Output with while loop

Author

Topic

robg69
Starting Member

41 Posts

Posted - 2004-07-07 : 14:46:05

Hey guys/gals:
I am building some scripts to insert default data into some of my tables. I'm trying to do the following:

declare @Amt int
while @Amt < 30
begin
select @ALL = 'insert into table (ID, Desc) VALUES ( ' +
@Amt + ', ' + char(39) + Desc + char(39) + ' )' from table
set @Amt = @Amt + 1
end

This works, however, it's not outputting values. So my question to you is how can I simply output the values like this:
insert into table (ID, Desc) VALUES ( 1, 'red' )
insert into table (ID, Desc) VALUES ( 2, 'blue' )
...
Instead of this:
The command(s) completed successfully.

I know this has to be really easy, I just can't seem to figure it out, any ideas?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 14:59:07

Add

SELECT @ALL

to your loop?

Or how about

EXEC(@ALL)

or both?

Brett

8-)

robg69
Starting Member

41 Posts

Posted - 2004-07-07 : 15:20:51

Yea I tried that, but it didn't work. I tried the following and it still just says:
The command(s) completed successfully.

declare @Amt int
declare @ALL varchar(8000)
while @Amt < 30
begin
select @ALL = 'insert into table (ID, Desc) VALUES ( ' +
@Amt + ', ' + char(39) + Desc + char(39) + ' )' from table

print 'hi: ' + @ALL
exec (@All)
set @Amt = @Amt + 1
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-07 : 16:28:20

You haven't set @Amt to anything. So it's NULL.


declare @Amt int
set @Amt = 0
declare @ALL varchar(8000)
while @Amt < 30
begin
	select @All = 'insert into table (ID, Desc) VALUES ( ' +
	convert(varchar(2), @Amt) + ', ' + char(39) + 'Desc' + char(39) + ' )' from table

	print @All
	set @Amt = @Amt + 1
end

Tara

robg69
Starting Member

41 Posts

Posted - 2004-07-07 : 16:45:52

I was, I just forgot to include it. I went back and edited my post. However I think you guys are missing my point. All I want is to output the values from the select. The select is a valid select from a table in my database. I'm just trying to append "insert into table..." and build my value list. At the very least, all I'm trying to do is get a running count like so:

declare @cnt int
while @cnt < 25
begin
select @cnt as cnt, ID, Desc from table
set @cnt = @cnt + 1
end

to get the following results:

@cnt,ID,Desc
1,1,red
2,2,blue
3,4,green
4,8,violet
etc

however, all I'm getting is:
"The command(s) completed successfully."

I hope I cleared things up a bit...

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-07 : 16:50:16

Did you run my code? It will output INSERT INTO statements for each row found in the table.

Your editted code is still not setting @Amt to anything. Nor is @cnt in your last post.

Tara

robg69
Starting Member

41 Posts

Posted - 2004-07-07 : 19:53:16

Oh, I see what you're saying, you have to set a default value...I new it was something simple that I was missing.

Thanks Tara!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources