How to load a temporary table from SQL Server or Tableau Desktop?

Question

I have three SQL queries results saved in batch in three temporary tables in a SQL Server database but these temp table seems to be not available in tableau while connecting to the database from tableau.

For example:

Create a temp table #p

CREATE TABLE #p 
(
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);

Insert values into #p from main table

insert into #p 
    select * 
    from Persons 
    where PersonID in (1, 2, 3);

After connecting to the SQL Server data source in tableau the #p temp table is not showing up in the table list. Is there any possible way I can use the temp table in tableau for dashboard? If not kindly suggest some alternatives on how to make these temp table available in dashboard.

I am struggling to solve this issue for past few days so hope to hear some suggestions from you guys soon..thank you!

Answer 1

Local temporary tables are session-scoped (it means they are only visible in the very same session where they have been generated).

You can try with Global Temporary Tables (SQL Server uses ## in front of their names) or normal tables if you want them to be visible in other sessions...

Both Local and Global Temporary tables will disappear when the session where they have been generated is closed.

Answer 2

Temporary tables(including global temporary tables) will be deleted automatically when no more connections are accessing them. Being said that, there are a couple of ways to achieve what you are trying to do in Tableau.

1. Initial SQL with Temp Table

Tableau has an 'Intial SQL' option that you can select when defining the connection. These statements will be executed every time Tableau make a connection to the server. please note that temporary tables are created in tempdb; so you have to change the database to tempdb. You also have to use fully qualified table name in your SQL. Please pay special attention to the highlighted areas.

2. Stored Procedure

If you have enough access rights, you can create your query as a stored procedure and call it from Tableau. Stored Procedures will be displayed as a separate section under your tables.