1

I am trying to create a global temp table using the results from one query, which can then be selected as a table and manipulated further several times without having to reprocess the data over and over.

This works perfectly in SQL management studio, but when I try to add the table through an Excel query, the table can be referenced at that time, but it is not created in Temporary Tables in the tempdb database.

I have broken it down into a simple example.

If I run this in SQL management studio, the result of 1 is returned as expected, and the table ##testtable1 is created in Temporary Tables

set nocount on;

select 1 as 'Val1', 2 as 'Val2' into ##testtable1
select Val1 from ##testtable1

I can then run another select on this table, even in a different session, as you'd expect. E.g.

Select Val2 from ##testtable1

If I don't drop ##testtable1, running the below in a query in Excel returns the result of 2 as you'd expect.

Select Val2 from ##testtable1

However, if I run the same Select... into ##testtable1 query directly in Excel, that correctly returns the result of 1, but the temptable is not created.

If I then try to run 

Select Val2 from ##testtable1

As a separate query, it errors saying "Invalid object name '##testtable1'

The table is not listed within Temporary Tables in SQL management studio.

It is as if it is performing a drop on the table after the query has finished executing, even though I am not calling a drop.

How can I resolve this?

Improve this question
4
  • 1
    Why do you want a global temp table in the first place? They are fraught with concurrency issues. Remember that a global table will be dropped when the last connection using it is closed. That means if you create in Excel but don't use it that temp table doesn't just hang around indefinitely. Commented Nov 7, 2017 at 15:26
  • Just to stress what @SeanLange already mentioned: SSMS (is able to) keeps session open while Excel cannot. Each time it sends a query a new session is created and immediately closed again (once the query completes). Commented Nov 7, 2017 at 15:30
  • Ultimately this is to create a financial balance sheet report for 5 brands that belong to a parent company. Each brand is a separate database. There are 5 queries that return the results, which then need to be further processed to add them all together as a "group total". There is no reason it could not all be done in one query, but each of the 5 queries is 22,000 characters long, so adding them all together pushes me way over the .ConnectionString character limit. Doing it this way works perfectly in Management Studio, it's just trying to get that data processing working via Excel. Commented Nov 7, 2017 at 15:44
  • are you closing the connection between each call? which version of excel are you using? are you using the built in query tool in excel for this or vba? and one last Q: can you create a a view or stored proc to do this? these can go across multiple dbs if required :D Commented Nov 7, 2017 at 16:12

3 Answers 3

Reset to default
2

Read up on global temp tables(GTT). They persist as long as there is a session referencing it. In SSMS, if you close the session that created the GTT prior to using it in another session, the GTT would be discarded. This is what is happening in Excel. Excel creates a connection, executes and disconnects. Since there are no sessions using the GTT when Excel disconnects, the GTT is discarded.

I would highly recommend you create a normal table rather than use a GTT. Because of their temporary nature and dependence on an active session, you may get inconsistent results when using a GTT. If you create a normal table instead, you can be certain it will still exist when you try to use it later.

The code to create/clean the table is pretty simple.

IF OBJECT_ID('db.schema.tablename') IS NOT NULL
    TRUNCATE TABLE [tablename]
ELSE
    CREATE [tablename]...
GO

You can change the truncate to a delete to clean up a specific set of data and place it at the start of each one of your queries.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the suggestion :) I have got it working exactly as I wanted just by using normal tables. I call a drop on the table as soon as I have finished using it.
0

is it possible you could use a view? assuming that you are connecting to 5 DBs on the same server can you union the data together in a view:

    CREATE VIEW [dbo].[testView]
    AS
    SELECT        *
    FROM            database1.dbo.myTable
    UNION
    SELECT        *
    FROM            database2.dbo.myTable

Then in excel:

Data> New Query > From Database > FromSQL Server Database

enter DB server

Select the view from the appropriate DB - done :)

OR call the view however you are doing it (e.g. vba etc.)

equally you could use a stored procedure and call that from VBA .. basically anything that moves more of the complexity to the server side to make your life easier :D

Improve this answer

Comments

0

You can absolutely do this. Notice how I'm building a temp table from SQL called 'TmpSql' ...this could be any query you want. Then I set it to recordset 1. Then I create another recordset 2, that goes and gets the temp table data.

Imagine if you were looping on the first cn.Execute where TmpSql is changing.. This allows you to build a Temporary table coming from many sources or changing variables. This is a powerful solution. 

cn.open "Provider= ..."

sql = "Select t.* Into #TTable From (" & TmpSql & ") t "
Set rs1 = cn.Execute(sql)

GetTmp = "Select * From #TTable"
rs2.Open GetTmp, cn, adOpenDynamic, adLockBatchOptimistic
If Not rs2.EOF Then Call Sheets("Data").Range("A2").CopyFromRecordset(rs2)

rs2.Close
rs1.Close

cn.Close
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.