问题描述:

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?

网友答案:

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
网友答案:

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
网友答案:

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
相关阅读:
Top