none
Insert data from #temp table to #temp table

    Question

  • Hi Guys,

    I am using following query to insert data from #temp table to #table with Pivot. But after inserting data into #Final table, when i select #Final table, it does not exist.

    Could you please help?

    		SET @query = 'select * INTO #Final from #temptable PIVOT (max(Name) FOR ID IN (' + @code + ')) pvt Order by 2'
    		EXEC (@query)

    Thank you in advance!!
    Wednesday, January 02, 2013 9:13 PM

Answers

  • Append the Select syntax of  table in the query itself.Try the below Query

    SET @query = 'select * INTO #Final from #temptable PIVOT (max(Name) FOR ID IN (' + @code + ')) pvt Order by 2 select * from #Final '
    EXEC (@query)
    ------------------
    ----------------------
    --------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Vick72 Thursday, January 03, 2013 2:40 PM
    Thursday, January 03, 2013 8:09 AM

All replies

  • (7771 row(s) affected)
    (386 row(s) affected)
    Msg 208, Level 16, State 0, Line 61
    Invalid object name '#Final'.

    Wednesday, January 02, 2013 9:15 PM
  • Can you explain the purpose of your query? Try

    SET @query = 'select * from #temptable PIVOT (max(Name) FOR ID IN (' + @code + ')) pvt Order by 2'
    		EXEC (@query)

    If you use the dynamic query with INTO #Final that new #Final table will exist only in the scope of dynamic query and you will not be able to access that table outside dynamic sql.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, January 02, 2013 10:00 PM
  • Hi Vick72

    It's not obvious at first but the temp table create in the dynamic SQL is considered to be in another session. Since temp tables are session specific you wont find it in the same session window (outside of the dynamic query. You might be able to use a global temp table "##temp".


    Pérez

    Wednesday, January 02, 2013 10:09 PM
  • Hi Vick72

    Another possible solution is to create the temp table #Final outside of the dynamic SQL.

    Then use an insert with the dynamic SQL Naomi N posted. Something like:

    create table #Final (<column1>,<column2>,<column3>,<etc.>)
    SET @query = 'select * from #temptable PIVOT (max(Name) FOR ID IN (' + @code + ')) pvt Order by 2'
    INSERT INTO #Final
    EXEC (@query)


    Pérez


    • Edited by Peréz Wednesday, January 02, 2013 10:18 PM
    • Proposed as answer by Aalamjeet Rangi Thursday, January 03, 2013 12:08 AM
    • Unproposed as answer by Naomi NModerator Thursday, January 03, 2013 2:09 AM
    Wednesday, January 02, 2013 10:16 PM
  • Don't do that. Once you have produced your dynamic pivot return it to the client. There is nothing more you can do with in it SQL, since the dynamic pivot table is not really relational. Anything you would try to do will be utterly painful.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 02, 2013 11:24 PM
  • As your problem of #Final TableBetter to Create #Final

    And use insert statement.


    -- Bhavesh Thakkar

    Sr.Database Administrator
    My Blog
    Linked In Profile

    Thursday, January 03, 2013 5:34 AM
  • Hi 

    Simply Scope is the reason for Error ,

    Use Global Temp Table ##Final 


    Samith C Valsalan There is no Wrong time to do Right thing :) Please mark it as an answer/helpful if you find it as useful.

    Thursday, January 03, 2013 7:10 AM
  • Append the Select syntax of  table in the query itself.Try the below Query

    SET @query = 'select * INTO #Final from #temptable PIVOT (max(Name) FOR ID IN (' + @code + ')) pvt Order by 2 select * from #Final '
    EXEC (@query)
    ------------------
    ----------------------
    --------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Vick72 Thursday, January 03, 2013 2:40 PM
    Thursday, January 03, 2013 8:09 AM
  • Append the Select syntax of  table in the query itself.Try the below Query

    [code]
    SET @query = 'select * INTO #Final from #temptable PIVOT (max(Name) FOR ID IN (' + @code + ')) pvt Order by 2 select * from #Final '
    EXEC (@query)

    That is not going to work out. A temp table disappears when the scope it was created in exits, and the dynamic SQL is a scope of its own.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 03, 2013 12:07 PM
  • Thank you Ganesh!

    I used your query with Global ##Temp table... and its worked i aspected.

    Thank you.

    Thursday, January 03, 2013 2:40 PM