Problems updating #tmp tables in loop

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

SQL Server Development (2000)

Problems updating #tmp tables in loop

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-23 : 12:05:43

I've got a while loop which happens to fail when I attempt to alter temporary tables by adding columns or updating values in it even though my SQL statements execute perfectly well outside of the loop. Does anyone know why this might be happening?

If you insist on an example, try this:

--RUN FROM START1 TO END1, THEN START2 TO END2, ETC. DROP AT END

--START1:
create table #source
(
Date_ smalldatetime not null,
ID varchar(3) not null,
Item1 int not null
)

declare @go int
declare @max int
select @go = 1
select @max = 10

while @go <= @max
begin
insert into #source values
('1990-12-31', 'ABC', @go)
select @go = @go +1
end

select * from #source --this first part works fine, #source is created with the column Item1 taking on successive values of @go

--END1:

--START2:
select * into #mytable from #source--#mytable created no problem

declare @max int
declare @go2 int
select @go2 = 1
select @max = 10
while @go2 <= @max
begin
alter table #mytable add Item2 int null -- but this fails because you can't seem to alter it in the loop
update #mytable set Item2 = @go2 where Item1 = @go2
select @go2 = @go2 + 1
end

--END2:

--START3:

alter table #mytable add Item2 int null --eventhough I can do this outside of the loop:
select * from #mytable

--END3

/*Drop these tables:
drop table #source
drop table #mytable
*/

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-05-23 : 12:43:49

You need to have a GO after a DDL change before another statement can recognize it, this signals to SQL Server the end of a batch and thus any new statements can see the new schema changes. You are not allowed GO between BEGIN and END statements. So you won't be able to use your while loop for both DDL and DML modifications.

Also if it did work you are adding column Item2 to the table more then once, this would fail since its not a unique column name.

You could accomplish this by seperating your DDL and DML while loops.

Mike Petanovitch

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-23 : 13:31:47

see
http://www.mindsdoor.net/SQLTsql/AccessTempTablesAcrossSPs.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-23 : 15:41:12

Thank you both. I appreciate your responses. I'll see what I can do to modify what I have.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources