drop temp table column

[slybitz]

How can I drop a temp table column if the column that I want to drop has data in it? I can drop a temp table column if there is no data in it via something like:

create table #temp
(
hid int,

greeting varchar(1),

defaultyn int
)

select * from #temp

alter table #temp drop column [$defaultyn]

select * from #temp

...but I cannot get the column to drop if the column has any data in it. I get an error that says "Invalid column name". here's what I'm trying that gives me the error:

create table #temp
(
hid int,

greeting varchar(1),

defaultyn int
)

insert into #temp
(
hid,

greeting,

defaultyn
)

select 
1,
'h',
0

select * from #temp

alter table #temp drop column [$defaultyn]

select * from #temp

Does anyone have any clues on how to drop a temp table column with data in it?

Thanks for your help!!

 

[mrdenny]

Remove the $ from the column name.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[slybitz]

mrdenny that was a typo. That $ should not be in there. When I take the $ out it still gives me the same error.

Any suggestions?

Here's what the code should look like that is giving me the error:

create table #temp
(
hid int,

greeting varchar(1),

defaultyn int
)

insert into #temp
(
hid,

greeting,

defaultyn
)

select
1,
'h',
0

select * from #temp

alter table #temp drop column defaultyn

select * from #temp

 

[mrdenny]

It appears to be a bug in SQL 2000. The code works fine in SQL 2005. Put a batch seperator between the commands and it works fine. I get the same error on my SQL 2000 boxes.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Qik3Coder]

just run an update statement on the col and NULLify it first. then there is no data in the column.


Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.