不能在sql server中删除一个临时表无效的对象名称“#Temp1”错误?

I have the following code which throws an Invalid object name '#Temp1' error....

我有下面的代码抛出一个无效的对象名称“# Temp1”错误....

Each iteration, i truncate the table (DROP table also did not work)....nothing obvious jumps out as a problem.....thanks for any ideas...

每次迭代，我截断了表(删除表也没有工作)没有什么明显的跳出来作为一个问题。谢谢你的任何想法…

While @@Fetch_Status = 0 -- For each ObsSetCode - 
            BEGIN           

                PRINT 'I am processing the SECTION ------ ' + @dl_EventSetName
                PRINT 'I am processing the ObsSetCode ------ ' + @ObsSetList

                IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
                BEGIN
                    TRUNCATE TABLE #Temp1
                END

                    Insert Into #Temp1 
                    Select o.EventSetName,
                           o.EventSetDisplay,
                           o.EventSetDescription,
                           o.ChildSetName,
                           ROW_NUMBER() Over (Order By o.ChildSetName) RN                  
                    From   ##ObsSetLevel o,
                           ##Final f
                    Where  f.ChildSetName = o.EventSetName and 
                           o.EventSetName = @ObsSetList 
                    Order By o.ChildSetName asc  

                    Insert into ##Final
                    Select *
                    From #Temp1
                    Where  RN = 1
                    Union
                    Select '', '', 
                           '', ChildSetName, RN
                    From #Temp1
                    Where RN > 1


                   Insert Into ##Final
                   Select distinct o.ChildSetName,
                          o.ChildSetName,
                          o.ChildSetName,
                          '',
                          ''
                   From   ##ObsSetLevel o,
                          ##Final f
                   Where f.ChildSetName = o.EventSetName and 
                         o.EventSetName = @ObsSetList     
                   Order By o.ChildSetName asc

                    PRINT @ObsSetList   
                    FETCH NEXT FROM ObsSetList_cursor Into @ObsSetList


            END

2 个解决方案

#1

You are not creating #Temp anywhere.

您没有在任何地方创建#Temp。

Drop the table if it is found.
如果找到的话，就把桌子放下。
Create the table with the first SELECT.
使用第一个SELECT创建表。

Try the following:

试试以下:

While @@Fetch_Status = 0 -- For each ObsSetCode - 
BEGIN           

    PRINT 'I am processing the SECTION ------ ' + @dl_EventSetName
    PRINT 'I am processing the ObsSetCode ------ ' + @ObsSetList

    IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
    BEGIN
        DROP TABLE #Temp1  /* DROP THE TABLE IF IT IS FOUND */
    END

        Select o.EventSetName,
               o.EventSetDisplay,
               o.EventSetDescription,
               o.ChildSetName,
               ROW_NUMBER() Over (Order By o.ChildSetName) RN                  
        Into   #Temp1 /* Create the table with your SELECT */
        From   ##ObsSetLevel o,
               ##Final f
        Where  f.ChildSetName = o.EventSetName and 
               o.EventSetName = @ObsSetList 
        Order By o.ChildSetName asc  
...

#2

Change you select to look like this:

改变你的选择如下:

Select o.EventSetName,
        o.EventSetDisplay,
        o.EventSetDescription,
        o.ChildSetName,
        ROW_NUMBER() Over (Order By o.ChildSetName) RN
into #Temp1                 
From   ##ObsSetLevel o,
       ##Final f
Where  f.ChildSetName = o.EventSetName and 
        o.EventSetName = @ObsSetList 
Order By o.ChildSetName asc

You cannot use Insert Into unless you have defined the table or it already exists.

除非已经定义了表或已经存在，否则不能使用Insert。

#1