October 12, 2022 at 9:13 pm
Okay so this might be a n00b question but it's something I've been wondering about. Say I have the following query:
drop table if exists #table
select 1 as foo, 2 as bar into #table
select foo, bar from #table
I can run this all day. But then, let's say I make the following change:
drop table if exists #table
select 1 as foo, 2 as bar, 3 as blah into #table
select foo, bar, blah from #table
When I run this, I get an error stating that column 'blah' is invalid. I know why this happens; the parser is looking at the existing #table and saying "You fool! There is no column 'blah' in that table!"
And so I have to run the drop table statement on it's own. Once that is done, the new version of the query runs just fine.
My question is: Is there any way to get SQL to automatically pick up the new #table and not use the existing temp table? I realize I can just drop the temp table at the end; but generally when this is an issue, it's because I am using the temp table to investigate something, so dropping it is counterproductive.
October 12, 2022 at 9:47 pm
I think you would have to add a GO, otherwise if the table already exists SQL Server won't be able to "compile" the SQL:
drop table if exists #table
go --<<--<<--
select 1 as foo, 2 as bar, 3 as blah into #table
select foo, bar, blah from #table
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 13, 2022 at 1:55 pm
True... I'd just have to make sure I declare my variables after the GO...
I was hoping for a more lazy approach I guess, but I think you're right 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply