[SA 7.0.158 Pro] Temp table odd behavior

[SA 7.0.158 Pro] Temp table odd behavior

Hi,

I'm using SQL Server 2012 (v. 11.0.2100.60) and have found some odd behavior.
The first is with temp tables.
If I have the following:

	Code:
	CREATE TABLE #tmp ( field1 INT, field2 INT, field3 INT, field4 INT, field5 INT, field6 INT ) INSERT INTO #tmp (field1, field2) VALUES (1, 2) SELECT * FROM #tmp t WHERE t.

The fields that will show up in the WHERE clause will only be field1 and field2. In order to get all fields from #tmp to display, I would need to have all of them in the INSERT statement. Is there a way around this? I don't recall seeing this with version 6 of SA however I don't believe I actually tried those steps.

Thanks!

Can confirm. I use SSMS 2008R2 and SA 7.

Not sure about SA6 behavior here.

It looks like it "assumes" structure of the temp table based on the last INSERT operation. Not good in this particular case.
On the other hand, there is no reliable way to figure out temp table structure in design time.

To demonstrate that, try adding between CREATE and INSERT, something like the following

	Code:
	IF datepart(DD, getdate()) > 15 BEGIN DROP TABLE #tmp CREATE TABLE #tmp (field1 int, field2 int) END

With the above block, the code will produce with different temp table structure on different days.[/code]

When dealing with temp tables, I've noticed that if I were to create a temp table on the fly
i.e.

	Code:
	SELECT field1, field2 INTO #1 FROM table1

it doesn't assume any structure

I tried your code which led me to try the following as well:

	Code:
	CREATE TABLE #tmp(field1 INT, field2 INT, field3 INT) SELECT * FROM #tmp t WHERE t. IF datepart(DD, getdate()) > 15 BEGIN DROP TABLE #tmp CREATE TABLE #tmp (f1 int, f2 int) END SELECT * FROM #tmp t WHERE t. CREATE TABLE #tmp (fld1 INT, fld2 VARCHAR(5), fld3 FLOAT) SELECT * FROM #tmp t WHERE t.

In all of these cases it will assume the most recent CREATE TABLE structure. This is regardless of the IF statement. So since today is < 15, in practice the second CREATE TABLE statement would not execute however it's understandable that it cannot resolve a statement such as that in design mode. I believe this to be an acceptable feature.

I thought I'd give a try to see what would happen if, instead of creating a temp table I would create a physical one but without actually executing the statement:

	Code:
	CREATE TABLE temptable (field1 INT, field2 INT, field3 INT, field4 INT) INSERT INTO temptable (field1, field2) VALUES(1,2) SELECT * FROM temptable t WHERE t.

Even though this temptable doesn't exist yet, it still assumes the correct structure.

Is it possible to not have SA re-assume a structure through the INSERT statement?