数据库中已经有一个名为'#Temp'的对象,与Temp表有关 [英] There is already an object named '#Temp' in the database,,related to Temp table
问题描述
我面临与临时桌有关的问题...
我创建了Storead程序。
以下是我在SP中的情景。
IF OBJECT_ID (' tempdb ..#temp') IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID (' tempdb ..#temp1') IS NOT NULL DROP TABLE #temp1;
IF OBJECT_ID (' tempdb ..#comman') IS NOT NULL DROP TABLE #comman;
如果 type = ' A'
BEGIN
选择 abc,d,e,f 进入 #temp 来自 mst_temp
END
ELSE
BEGIN
选择 abc,d,temp1 进入#temp1 来自 mst_temp
END
if type = ' A'
选择 * 进入 #comman 来自 #temp
else
选择 * 进入 #comman 来自#temp1
- - some在#comman表上操作
执行存储过程后我收到错误
' 已有一个名为' #comman ' 在数据库'
即使我有Drop临时表
请告诉我哪里错了!!!
Hello All,
我已经解决了这个问题,
IF OBJECT_ID (' tempdb ..#temp') IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID (' tempdb ..#temp1') IS NOT NULL DROP TABLE #temp1;
CREATE table #comman
(abc varchar ( 10 ),
d varchar (< span class =code-digit> 10 ),
e varchar ( 10 ),
f varchar ( 10 ));
如果 type = ' A'
BEGIN
选择 abc,d,e,f 进入 #temp 来自 mst_temp
END
ELSE
BEGIN
选择 abc,d,temp1,' ' as f into #temp1 来自 mst_temp
END
if type = ' A'
Insert < /跨度> into #comman(abc,d,e,f) from #temp
else
将 插入 #comman(abc ,d,e,f)来自#temp1
选择 * 来自 #comman;
drop table #comman
我刚使用insert代替select并创建表结构
becoz选择它创建新表。
谢谢大家
我可以在查询中看到一个问题。
IF OBJECT_ID (' tempdb ..#temp1') IS NOT NULL DROP TABLE #temp1;
IF OBJECT_ID (' tempdb ..#comman') IS NOT NULL DROP TABLE #comman;
这里的临时表名称不正确。
名称后面有额外的空格。
而不是
1.OBJECT_ID(''tempdb ..#temp1'')
,它是写得像
OBJECT_ID(''tempdb ..#temp1'')
2.OBJECT_ID(''tempdb ..#comman'')
,它写成
OBJECT_ID(' 'tempdb ..#comman'')
所以请在下面尝试...
< pre lang =sql> IF OBJECT_ID (' tempdb ..#temp') IS NOT NULL DROP 表#温度;
IF OBJECT_ID (' tempdb ..#temp1') IS NOT NULL DROP TABLE #temp1;
IF OBJECT_ID (' tempdb ..#comman') IS NOT NULL DROP TABLE #comman;
你好,
这是一个解析器/编译问题,它无法检测到有一个Drop语句。
检查以下链接,如果它运作良好,否则以下是2个替代解决方案。 [可能看起来很丑但应该工作]
http://stackoverflow.com/questions/4245444/there-is-already-an-object-named-columntable-in-the-database [ ^ ]
解决方案1:
1.删除临时表(如果存在)
2.创建一个包含所需列的临时表。
3.使用select语句将值插入临时表。
解决方案2:
IF OBJECT_ID (' tempdb ..#comman') IS NOT NULL DROP TABLE #comman;
IF OBJECT_ID (' tempdb ..#comman1') IS NOT NULL DROP TABLE #comman1;
IF OBJECT_ID (' tempdb ..#comman2') IS NOT NULL DROP TABLE #comman2;
如果 type = ' A'
选择 * 进入 #comman 来自 #temp
else
选择 * into #comman1 来自#temp1
IF OBJECT_ID (' tempdb ..#comman1') IS NOT NULL
< span class =code-keyword>选择 * 进入#comman2 来自 #comman
else
选择 * 进入#comman2 来自#comman1
Hi,
I am facing problem related to Temporary table...
I have create Storead procedure.
below is my scenario in SP.
IF OBJECT_ID('tempdb..#temp' ) IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1;
IF OBJECT_ID('tempdb..#comman') IS NOT NULL DROP TABLE #comman ;
If type ='A'
BEGIN
select abc,d,e,f into #temp from mst_temp
END
ELSE
BEGIN
select abc,d,temp1 into #temp1 from mst_temp
END
if type='A'
select * into #comman from #temp
else
select * into #comman from #temp1
---- some operation on #comman table
After Executing Stored prcedure I got error
'There is already an object named '#comman ' in the database'
Even though I have Drop temporary table
Please tell me where i am wrong !!!
Hello All,
I have resolved this problem,
IF OBJECT_ID('tempdb..#temp' ) IS NOT NULL DROP TABLE #temp; IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1; CREATE table #comman ( abc varchar(10), d varchar(10), e varchar(10), f varchar(10) ); If type ='A' BEGIN select abc,d,e,f into #temp from mst_temp END ELSE BEGIN select abc,d,temp1,'' as f into #temp1 from mst_temp END if type='A' Insert into #comman(abc,d,e,f) from #temp else Insert into #comman(abc,d,e,f) from #temp1 select * from #comman; drop table #comman
I have just used insert instead of select and create table structure
becoz due to select its create new table.
Thanks all of you
I can see one problem in the query.
IF OBJECT_ID('tempdb..#temp1 ') IS NOT NULL DROP TABLE #temp1; IF OBJECT_ID('tempdb..#comman ') IS NOT NULL DROP TABLE #comman ;
Here the Temporary tables names are not correct.
There are extra spaces after the names.
Instead of
1.OBJECT_ID(''tempdb..#temp1'')
, it is written like
OBJECT_ID(''tempdb..#temp1 '')
2.OBJECT_ID(''tempdb..#comman'')
, it is written like
OBJECT_ID(''tempdb..#comman '')
So do like below and try...
IF OBJECT_ID('tempdb..#temp' ) IS NOT NULL DROP TABLE #temp; IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1; IF OBJECT_ID('tempdb..#comman') IS NOT NULL DROP TABLE #comman;
Hello,
This is a parser/compile issue, it is not able to detect that there is a Drop statement.
Check below link, if it works well and good, otherwise below are 2 alternate solutions. [ may look ugly but should work]
http://stackoverflow.com/questions/4245444/there-is-already-an-object-named-columntable-in-the-database[^]
Solution 1:
1. Drop temp table, if it exists
2. Create a temp table with required columns.
3. insert values into the temp table using select statement.
Solution 2:
IF OBJECT_ID('tempdb..#comman ') IS NOT NULL DROP TABLE #comman ; IF OBJECT_ID('tempdb..#comman1 ') IS NOT NULL DROP TABLE #comman1 ; IF OBJECT_ID('tempdb..#comman2 ') IS NOT NULL DROP TABLE #comman2 ; if type='A' select * into #comman from #temp else select * into #comman1 from #temp1 IF OBJECT_ID('tempdb..#comman1 ') IS NOT NULL select * into #comman2 from #comman else select * into #comman2 from #comman1
这篇关于数据库中已经有一个名为'#Temp'的对象,与Temp表有关的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!