• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

SQL - If statement with temp table

IF @ID =1
 BEGIN
	select field1
	INTO #tmpCurrent
	FROM TABLE1
 END
ELSE IF @ID = 2
 BEGIN
	
	select field1, field2
	INTO #tmpCurrent
	FROM TABLE2
 END

Open in new window

this gives me an error

There is already an object named '#tmpCurrent' in the database.
0
Ess Kay
Asked:
Ess Kay
3 Solutions
 
Harish VargheseProject LeaderCommented:
Hello,

This is not possible since the compiler thinks that the table is already created in first condition. Either use different table names, OR create the table before the IF block and insert data into the table in your current blocks.

-Harish
0
 
Surendra NathCommented:
Yes, this wont work with the temporarily, its a suprise though.... it works with the permanent tables.


you can achieve the same results by using a permemnant as below

IF OBJECT_ID('tmpCurrent') IS NOT NULL
    DROP TABLE  tmpCurrent

declare @T TABLE 
(
A INT, B INT
)

DECLARE @ID INT 
SET @ID = 1
IF @ID =1
 BEGIN

	select A
	INTO tmpCurrent
	FROM @T
 END
ELSE IF @ID = 2
 BEGIN
	select A,B
	INTO tmpCurrent
	FROM @T
 END

SELECT * INTO #tmpCurrent FROM tmpCurrent
drop table tmpCurrent

Open in new window

0
 
PadawanDBACommented:
a little more context would be helpful...  Is this in a loop or no ?  If not, you could include something like:

if( object_id( N'tempdb..#tmpCurrent' ) is not null ) drop table #tmpCurrent;

Open in new window


before the select into.  Or you could actually create the temp table outside of the if statement (with the two columns) and then use an insert into inside of the if statement.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Scott PletcherSenior DBACommented:
Please try this:

IF @ID =1
 BEGIN
    begin try
    drop table #tmpcurrent
    end try
    begin catch
    end catch

      select field1
      INTO #tmpCurrent
      FROM TABLE1
 END
ELSE IF @ID = 2
 BEGIN
    begin try
    drop table #tmpcurrent
    end try
    begin catch
    end catch      
     select field1, field2
      INTO #tmpCurrent
      FROM TABLE2
 END

[Of course using a permanent table name instead is not the same thing, since multiple people running the code at the same time would destroy each others' tables.]
0
 
Ess KayEntrapenuerAuthor Commented:
thanks guys i solved it

Apparently you can't do it, the compiler check stops it
0
 
Scott PletcherSenior DBACommented:
You should look at this q again.  You can do it, and I posted code that actually does it :-)
0
 
Ess KayEntrapenuerAuthor Commented:
nope, i tried something similar to no avail scott, if it had worked, this question qouldnt be here
0
 
Scott PletcherSenior DBACommented:
Interesting ... I ran that code and it worked fine for me.

Are you talking about the code with the BEGIN TRY ... END CATCH construct?
0
 
Ess KayEntrapenuerAuthor Commented:
basically, i took this code
IF @ID =1
 BEGIN
	select field1
	INTO #tmpCurrent
	FROM TABLE1
 END
ELSE IF @ID = 2
 BEGIN
	
	select field1, field2
	INTO #tmpCurrent
	FROM TABLE2
 END


Select * from #tmpCurrent

Open in new window






and changed to


IF @ID =1
 BEGIN
	select field1
	INTO #tmpCurrent
	FROM TABLE1
Select * from #tmpCurrent
 END
ELSE IF @ID = 2
 BEGIN
	
	select field1, field2
	INTO #tmpCurrent2
	FROM TABLE2
Select * from #tmpCurrent2
 END

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now