Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
Insert data from #temp table to #temp table

已答覆 Insert data from #temp table to #temp table

  • Wednesday, January 02, 2013 9:13 PM
     
      Has Code
    Sign In to Vote
    0

    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!!
     

All Replies

  • Wednesday, January 02, 2013 9:15 PM
     
      Has Code
    Sign In to Vote
    0
     
    (7771 row(s) affected)
(386 row(s) affected)
Msg 208, Level 16, State 0, Line 61
Invalid object name '#Final'.

     
  • Wednesday, January 02, 2013 10:00 PM
    Moderator
     
     Proposed Has Code
    Sign In to Vote
    0

    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:09 PM
     
     
    Sign In to Vote
    1

    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:16 PM
     
      Has Code
    Sign In to Vote
    0

    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


     
  • Wednesday, January 02, 2013 11:24 PM
     
     
    Sign In to Vote
    0

    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
     
  • Thursday, January 03, 2013 5:34 AM
     
     
    Sign In to Vote
    0

    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 7:10 AM
     
     
    Sign In to Vote
    1

    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 8:09 AM
     
     Answered Has Code
    Sign In to Vote
    0

    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 12:07 PM
     
     
    Sign In to Vote
    0

    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 2:40 PM
     
     
    Sign In to Vote
    0

    Thank you Ganesh!

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

    Thank you.