SQL - If statement with temp table

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;

Select allOpen 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.

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.]

You should look at this q again.  You can do it, and I posted code that actually does it :-)

nope, i tried something similar to no avail scott, if it had worked, this question qouldnt be here

Interesting ... I ran that code and it worked fine for me.

Are you talking about the code with the BEGIN TRY ... END CATCH construct?

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

Select allOpen 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

Select allOpen in new window