Does anyone knows a work-around on how to have Workbench be able to read Temp tables?
Are you able to save the temp table into a format that could be easily uploaded via workbench? .CSV comes readily to mind here as a possible format for a workaround.
@Giancarlo, did the above reply by nalbright help address your question?
So this problem is caused because Workbench is asking the database server for the schema, but the database server (SQL Server in this case) is returning an error that it doesn't know the schema of the query due to the use of the temp table. The error will look something like:
ERROR  [Microsoft][SQL Server Native Client 11.0][SQL Server]The metadata could not be determined because statement 'INSERT INTO #temp1 SELECT 2 As Col' uses a temp table.
One workaround that I am aware of is to use T-SQL to explicitly specify the schema. So instead of:
SELECT 1 As ColINTO #temp1INSERT INTO #temp1SELECT 2 As ColSELECT Col FROM #temp1DROP TABLE #temp1
You could execute something like:
EXEC ('SELECT 1 As ColINTO #temp1INSERT INTO #temp1SELECT 2 As ColSELECT Col FROM #temp1DROP TABLE #temp1')WITH RESULT SETS (( Col int ))