What am I missing?
-
Okay... Regarding the following:
if OBJECT_ID('tempdb..#MyTable') is not null drop table #MyTable
select top 0 * into #MyTable from MyTable
alter table #MyTable add constraint PK_MyTable primary key (MyTableID)
alter table #MyTable add NewField char(1)
--go
select * from #MyTable
--exec ('select * from #ProductFreightRate')
It deletes a temp table if it exists, recreates it from an existing table, and adds a column to it. Then I want to select everything from the tamp table. When I do this, though, it tells me that the NewField doesn't exist. IF, however, I wrap the select statement in an execute command (as shown in the commented code) instead of a direct select statement, it works fine. Why?? Also, if I uncomment the "go", it works too (although this is no good since I want to use this stuff in a stored procedure). Again, why?? Am I missing something obvious here?
-
You are creating a LOCAL temporary table. The table isn't usable outside the SCOPE that it was created in.
Try making it a GLOBAL temporary table. Use two #'s (##temptablename) instead of one.
-SQLBill
Refer to the BOL for more information.
BOL=Books OnLine = SQL Server's Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
-
No, I tried that too. It didn't seem to have any effect on the situation. Any other ideas?
-
You haven't posted the relevant information. Does this work for you?
use pubs
go
select top 0 * into #MyTable from authors
alter table #MyTable add constraint PK_MyTable primary key (au_id)
alter table #MyTable add NewField char(1)
select * from #MyTable
It works for me; I see the new column... So I believe there's some information you're not including with the script you posted.
--Jonathan -
I'm with you... But instead of selecting * in that last line, select only the new field - shouldn't make a difference, right?
Thus:
select top 0 * into #MyTable from authors
alter table #MyTable add constraint PK_MyTable primary key (au_id)
alter table #MyTable add NewField char(1)
select NewField from #MyTable
Now you'll definitely get an error regarding the new field.
-
I have never tried what you are trying. I usually explicitly create temp tables in my sp's because it helps reduce compiles. What is the point of adding the new field? Is there another way to reach your final goal?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Yes, there are any number of ways to get where I'm going, and I already have workarounds. At this point, I'm mostly just frustrated because this is a classic case of "it should work", and it'd definitely be the simplest way to accomplish what I was trying to do.
-
Well, obviously it makes a difference.
This issue has to do with the way the query optimizer works with a batch. The entire script is parsed and compiled; it doesn't work serially on each statement. It chokes on the last statement as it cannot parse the additional object based on its picture of the schema, which unfortunately doesn't include the ALTER TABLE statement.
--Jonathan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply