IF statements and CREATE TABLE statements

[StephenLeach]

I have an IF statement whereby a tempory table is created by a SELECT INTO command. If the IF statement evaluates to true then the data for the temporary table comes from one source and if the IF statement is FALSE then the data comes from a second source. The name of the temporary table is the same regardless.

e.g.

IF @value = 0
BEGIN
SELECT * INTO #Temp FROM TableA
END
ELSE
BEGIN
SELECT * INTO #Temp FROM TableB
END

When I run this I get an error stating that the table #Temp already exists.

It is as if when doing a SELECT INTO, SQL ignores the IF. I have check the IF statement is valid by replacing the SELECT INTO's with SELECT 'Case A' and SELECT 'Case B'.

Does anyone have any ideas or come across similar problems?

 

[JayKusch]

Before your IF add a line that says


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#Temp]


Thanks

J. Kusch

 

[pgtek]

hi
just change the table name to #temp1
and the other #temp2

 

[StephenLeach]

Jay,

Thanks I'll give it a go

---------------------------

pgtek,

I need to to refer to the tempory table later so I need it to have the same name regardless of where the data comes from. I didn't make that clear. Thanks anyway

Stephen

 

[SQLSister]

Create the temp table first then do the if part and use an insert statement instead of select into.