Enabling Workbench to read/recognize temp tables

Giancarlo
Giancarlo Domo Employee

Does anyone knows a work-around on how to have Workbench be able to read Temp tables? 

Comments

  • nalbright
    nalbright Contributor

    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.

    "When I have money, I buy books. When I have no money, I buy food." - Erasmus of Rotterdam
  • kshah008
    kshah008 Contributor

    @Giancarlo, did the above reply by nalbright help address your question?

  • jeremyhurren
    jeremyhurren Domo Employee

    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 [42000] [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 Col
    INTO #temp1

    INSERT INTO #temp1
    SELECT 2 As Col

    SELECT Col FROM #temp1
    DROP TABLE #temp1

    You could execute something like:

     

    EXEC ('
    SELECT 1 As Col
    INTO #temp1

    INSERT INTO #temp1
    SELECT 2 As Col

    SELECT Col FROM #temp1
    DROP TABLE #temp1
    ')
    WITH RESULT SETS (
    ( Col int )
    )

     

This discussion has been closed.