SQL Server Insert Into Temp table from a table variable

Question

I need to insert data in my table variable to a temp table but it gives me the following error.

Msg 208, Level 16, State 0, Procedure sp_CreateScenario_q2, Line 70
Invalid object name '#tmpp1'.

Here is the code

IF OBJECT_ID('tempdb..#tmpp1') IS NOT NULL
   DROP TABLE #tmpp1

INSERT INTO #tmpp1 
   SELECT  
       [PlanningHierarchyId] 
       ,[ProductReferenceId]
       ,[PlanningYear] 
       ,[PlanningSeason]
       ,[UpdatedBy] 
   FROM 
       @paramTable

Is there any way to do this?

score 4 · Accepted Answer · answered Sep 16 '15 at 09:37

Error 'Invalid object name '#tmpp1' occurs because you delete temp table and then try to insert in it.

Try to use:

IF OBJECT_ID('tempdb..#tmpp1') IS NOT NULL
   DROP TABLE #tmpp1
            SELECT  
                 [PlanningHierarchyId] 
                ,[ProductReferenceId]
                ,[PlanningYear] 
                ,[PlanningSeason]
                ,[UpdatedBy]
            INTO #tmpp1  
            FROM @paramTable

score 3 · Answer 2 · answered Sep 16 '15 at 09:37

you are dropping table. Either create one with CREATE or use select * into instead of insert into

  IF OBJECT_ID('tempdb..#tmpp1') IS NOT NULL
   DROP TABLE #tmpp1 

            SELECT  
                 [PlanningHierarchyId] 
                ,[ProductReferenceId]
                ,[PlanningYear] 
                ,[PlanningSeason]
                ,[UpdatedBy] into #tmpp1
            FROM @paramTable

score 2 · Answer 3 · edited May 23 '17 at 12:14

Insert into is used when table already exists use SELECT into from . When you're trying to insert in temp table, temp table doesn't exists.

Refer : INSERT INTO vs SELECT INTO

 IF OBJECT_ID('tempdb..#tmpp1') IS NOT NULL
       DROP TABLE #tmpp1;

                SELECT  
                     [PlanningHierarchyId] 
                    ,[ProductReferenceId]
                    ,[PlanningYear] 
                    ,[PlanningSeason]
                    ,[UpdatedBy] 
                    INTO #tmpp1
                FROM @paramTable

SQL Server Insert Into Temp table from a table variable

3 Answers3