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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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