我有以下代码,它引发了无效对象名"#Temp1"错误。。。。
每次迭代,我都会截断表(DROP表也不起作用)。。。。没有什么明显的问题。。。。。谢谢你的任何想法。。。
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
您不会在任何位置创建#Temp
。
- 如果找到该表,请将其删除
- 使用第一个
SELECT
创建表
尝试以下操作:
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
...
将您选择的更改为如下所示:
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
除非已定义该表或该表已存在,否则不能使用Insert Into
。