SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
temp tables intellisense

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
temp tables intellisense
Author Message
SergiyR



Joined: 02 Sep 2016
Posts: 11
Country: Ukraine

Post temp tables intellisense Reply with quote
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/

Fri Sep 02, 2016 6:10 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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]
Fri Sep 02, 2016 10:39 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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.
Fri Sep 02, 2016 1:31 pm View user's profile Send private message
SergiyR



Joined: 02 Sep 2016
Posts: 11
Country: Ukraine

Post Reply with quote
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!!
Sun Sep 04, 2016 8:33 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Mon Sep 05, 2016 5:27 am View user's profile Send private message
SergiyR



Joined: 02 Sep 2016
Posts: 11
Country: Ukraine

Post Reply with quote
Ok
Tue Sep 06, 2016 12:25 am View user's profile Send private message
SergiyR



Joined: 02 Sep 2016
Posts: 11
Country: Ukraine

Post Reply with quote
Guys, I followed your instructions -- works perfectly.

Thanks again!!
Fri Sep 09, 2016 10:13 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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.
Mon Nov 07, 2016 4:07 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.