select into based on a parameter

[113983]

I have need to write program something like below

if @pnumber=1
begin
select * into #all from table1 where code = 9
end
else
begin
select * into #all from table1 where secondcode=99
end

when I try this I get #all already exists even though I am executing the else portion of above.

"There is already an object named '#all' in the database."


Is there a way around this?

 

[markros]

No, the only way around is to create #all table in advance using CREATE TABLE #All command.

PluralSight Learning Library

 

[gmmastros]

Like Markros said, the table must already exist. There is a trick you can use.

[!]
select * into #all from table1 where 1=0
[/!]
if @pnumber=1
begin
  Insert Into #All Select * from table1 where code = 9
end
else
begin
  Insert Into #All select * from table1 where secondcode=99
end

The first line, in red, will create the #All table with the correct table structure, but without any rows. The subsequent insert into's will copy the correct data in to it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom