|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Conditional select into on temp tableTrying to do the following results in the error 'there is already an object
named #TmpJ in the database.' I assure you there is not. #TmpJ is to be created with the following select into statement. So why is it complaining? Thank you for your insight. declare @a int set @a = 1 If @a = 0 select * into #TmpJ from Table1 where x = 52 else select * into #TmpJ from Table1 where x = 98 Why not just use a case statement:
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=469 -- Show quote2004 and 2005 Microsoft MVP C# Robbe Morris http://www.masterado.net Earn $$$ money answering .NET Framework messageboard posts at EggHeadCafe.com. http://www.eggheadcafe.com/forums/merit.asp "wxd" <w**@discussions.microsoft.com> wrote in message news:B63519AC-C9E6-45B9-B6E2-8F2F72466DEF@microsoft.com... > Trying to do the following results in the error 'there is already an > object > named #TmpJ in the database.' I assure you there is not. #TmpJ is to be > created with the following select into statement. So why is it > complaining? > Thank you for your insight. > > declare @a int > set @a = 1 > > If @a = 0 > select * into #TmpJ > from Table1 > where x = 52 > else > select * into #TmpJ > from Table1 > where x = 98 > Is this running in a stored procedure, or are you doing this interactively
from Query Analyzer (QA)? If you are using QA, have you tried running it multiple times in the same session? If so, then it was probably created the first time you ran it, but was not cleaned up - so it does exist (it actually exists in the TempDB database). The temp table will not get cleaned up until you end your QA session. As a side note, you could probably change the SQL so you did not need to duplicate the SELECT portion: select * into #TmpJ from Table1 where x = case when @a = 1 then 52 else 98 end IHTH Jeremy Show quote "wxd" <w**@discussions.microsoft.com> wrote in message news:B63519AC-C9E6-45B9-B6E2-8F2F72466DEF@microsoft.com... > Trying to do the following results in the error 'there is already an object > named #TmpJ in the database.' I assure you there is not. #TmpJ is to be > created with the following select into statement. So why is it complaining? > Thank you for your insight. > > declare @a int > set @a = 1 > > If @a = 0 > select * into #TmpJ > from Table1 > where x = 52 > else > select * into #TmpJ > from Table1 > where x = 98 > Am doing this in a stored procedure.
I made sure the table did not exist first when testing it too. First thing I thought of. Even switch the tmp table name to be sure. Maddening, isn't it? Why does it think the table is there? The case statement suggestion does work (thank you both), but I am curious as to why SQL thinks this temp table exists. Show quote "Jeremy Williams" wrote: > Is this running in a stored procedure, or are you doing this interactively > from Query Analyzer (QA)? > > If you are using QA, have you tried running it multiple times in the same > session? If so, then it was probably created the first time you ran it, but > was not cleaned up - so it does exist (it actually exists in the TempDB > database). The temp table will not get cleaned up until you end your QA > session. > > As a side note, you could probably change the SQL so you did not need to > duplicate the SELECT portion: > > select * into #TmpJ > from Table1 > where x = case when @a = 1 then 52 else 98 end > > IHTH > Jeremy > > "wxd" <w**@discussions.microsoft.com> wrote in message > news:B63519AC-C9E6-45B9-B6E2-8F2F72466DEF@microsoft.com... > > Trying to do the following results in the error 'there is already an > object > > named #TmpJ in the database.' I assure you there is not. #TmpJ is to be > > created with the following select into statement. So why is it > complaining? > > Thank you for your insight. > > > > declare @a int > > set @a = 1 > > > > If @a = 0 > > select * into #TmpJ > > from Table1 > > where x = 52 > > else > > select * into #TmpJ > > from Table1 > > where x = 98 > > > > > Yup - that's a head scratcher. The local temp table should be dropped upon
completion of the stored procedure execution. Have you tried adding an explicit DROP TABLE #TmpJ at the end of the SP? Also, this does not answer your question, but you might want to try using a table variable instead of a temporary table. That might get you worked around the issue. Show quote "wxd" <w**@discussions.microsoft.com> wrote in message news:C9963C1E-0DEB-4253-A7A8-E7836217B9EA@microsoft.com... > Am doing this in a stored procedure. > > I made sure the table did not exist first when testing it too. First thing > I thought of. Even switch the tmp table name to be sure. Maddening, isn't > it? Why does it think the table is there? > > The case statement suggestion does work (thank you both), but I am curious > as to why SQL thinks this temp table exists. > > > "Jeremy Williams" wrote: > > > Is this running in a stored procedure, or are you doing this interactively > > from Query Analyzer (QA)? > > > > If you are using QA, have you tried running it multiple times in the same > > session? If so, then it was probably created the first time you ran it, but > > was not cleaned up - so it does exist (it actually exists in the TempDB > > database). The temp table will not get cleaned up until you end your QA > > session. > > > > As a side note, you could probably change the SQL so you did not need to > > duplicate the SELECT portion: > > > > select * into #TmpJ > > from Table1 > > where x = case when @a = 1 then 52 else 98 end > > > > IHTH > > Jeremy > > > > "wxd" <w**@discussions.microsoft.com> wrote in message > > news:B63519AC-C9E6-45B9-B6E2-8F2F72466DEF@microsoft.com... > > > Trying to do the following results in the error 'there is already an > > object > > > named #TmpJ in the database.' I assure you there is not. #TmpJ is to be > > > created with the following select into statement. So why is it > > complaining? > > > Thank you for your insight. > > > > > > declare @a int > > > set @a = 1 > > > > > > If @a = 0 > > > select * into #TmpJ > > > from Table1 > > > where x = 52 > > > else > > > select * into #TmpJ > > > from Table1 > > > where x = 98 > > > > > > > > > When SQL Server prepares to compile a script, it doesn't use
flow-of-logic in its consideration; al it sees are two SELECT INTO statements using the same target name. You should either use a table variable OR pre-build your temp table before filling it. HTH, Stu You could rewrite it as:
-- create temp table select * into #TmpJ from Table1 where 0=1 -- insert data If @a = 0 insert into #TmpJ select *from Table1 where x = 52 else select *from Table1 where x = 98 Q Show quote "wxd" wrote: > Trying to do the following results in the error 'there is already an object > named #TmpJ in the database.' I assure you there is not. #TmpJ is to be > created with the following select into statement. So why is it complaining? > Thank you for your insight. > > declare @a int > set @a = 1 > > If @a = 0 > select * into #TmpJ > from Table1 > where x = 52 > else > select * into #TmpJ > from Table1 > where x = 98 > Take a look at this example which will help you work around the issue you are
facing you could also use dynamic sql to help trick the system into allowing you to select into but this solution works as well. Create table #Table1 ( x int ) Create table #TmpJ ( x int ) Insert #table1 Values (52) Insert #table1 Values (98) declare @a int SET @a = 0 If @a = 1 BEGIN INSERT #TMPJ select * from #Table1 where x = 52 END ELSE Begin INSERT #TMPJ select * from #Table1 where x = 98 end select * from #TmpJ select * from #Table1 Drop table #Table1 Drop table #TmpJ Hope this helps Show quote "wxd" wrote: > Trying to do the following results in the error 'there is already an object > named #TmpJ in the database.' I assure you there is not. #TmpJ is to be > created with the following select into statement. So why is it complaining? > Thank you for your insight. > > declare @a int > set @a = 1 > > If @a = 0 > select * into #TmpJ > from Table1 > where x = 52 > else > select * into #TmpJ > from Table1 > where x = 98 > SQL Server will not allow this statement.
you have to create the temp table first then only use INSERT statement to insert data into your temp table. SQL Server's syntax checker will block this kind of declaration. Leo Show quote "wxd" wrote: > Trying to do the following results in the error 'there is already an object > named #TmpJ in the database.' I assure you there is not. #TmpJ is to be > created with the following select into statement. So why is it complaining? > Thank you for your insight. > > declare @a int > set @a = 1 > > If @a = 0 > select * into #TmpJ > from Table1 > where x = 52 > else > select * into #TmpJ > from Table1 > where x = 98 >  |
|||||||||||||||||||||||
Other interesting topics