temp tables intellisense

Hi,

I'm evaluating the trial version of SQL Assistant and noticed some incorrect behavior.
I’m using this code to input data into temporary table:

Code:

SELECT
st.StudentId
,st.Id AS RowInRtoCnS
,st.CourseId
,st.CourseAttempt
,st.AgentName
,rarg.AgentId
,a.ComissionRate
INTO #tmpAgents
from RtoStudentRecord as st

inner join
(select max(RtoReportDate) as MaxDAte
from RtoStudentRecord) as grst
on st.RtoReportDate = grst.MaxDAte

LEFT JOIN
(SELECT
rar.AgentId,
rar.AgentName FROM RtoAgentRecord AS rar
GROUP BY rar.AgentId, rar.AgentName) AS rarg
ON rarg.AgentName = st.AgentName

LEFT JOIN Agent AS a ON a.RtoManagerId = rarg.AgentId

As I’m trying to derrive data from this temp table in the same batch, I found that this table is not present in the tables pop-up window which appears when the query is combined. Thus I have to type query to this table totally in manual mode.
Thanks if you have any suggestions how to turn on intellisense related to working with temp tables!

http://shot.qip.ru/00Qqzx-3Vmk2GJiW/

I guess that would be a rather complex task. First of all, that table might not even exist at the time you'd like to get it's structure from SA. You're selecting INTO it, that is, it is going to get created when you execute your query, therefore it would have to predict the state of temporary table in the tempdb after the execution of the query.
And even if it does exist, it sits in tempdb (in case of SQL Server) which is most likely a different database than the one you're working in.

The only workaround I found is to explicitly create the temporary table with, for example:

	Code:
	CREATE TABLE #tmpAgents(etc, etc)

This way when you're below the point where it has been created you'll get it (and its properties) in the popup. Now that brings up the problem of convenience: it might be an extremely boring and error prone thing to assign a name and type to all those columns that SQL Server could do it on its own. I bypass that one by using a snippet that transforms the selected statements (without the INTO part) into a

	Code:
	CREATE TABLE #temp_your_name (<everything>); INSERT INTO #temp_your_name <original_select_statement>;

after which the temporary table can be used from the pop-up. You can find the snippet here.[/i]

If you define your temp table using CREATE TABLE statement and then INSERT INTO that table instead of SELECT...INTO... then you will see SQL intelisense suggestions for the temp table.

Thanks for suggestions, guys!!
I shall definitely use them the closest time that i shall work with temp tables (this time i wrote the code i needed manually).

Yet, to make things clear, firstly I executed SELECT ... INTO statement and only after this I was hoping to see #tempTable structure in pop-up. So the table was already created. I have used temp tables in several session of working with SQL Assistant, and somitimes I got strucrure of the table and sometimes don't. If i notice any regularuty in this, i shall definitely let you know

Thanks again!!

SysOp already described the pattern. Whenever you explicitly create the temp table, you're going to get its structure in popup. If you only SELECT INTO it or use a temp table created elsewhere (eg. in a stored procedure called earlier) you'll be deprived of it.

Guys, I followed your instructions -- works perfectly.

Thanks again!!

Not to make a new topic, but I'm running into an issue where temp table seems to work ok but the syntax checker tells me the object doesn't exist but for only ONE of my two #tempTables that are created upon script execution.

So my questions are

1) Why does the syntax checker not see that I've created the table before hand
2) Why does the syntax checker not recognize BOTH of my #tempTables as "does not exist" during a syntax check?

When running the script as a whole it works as it should.