Posted by Teresa Masino on 04/12/06 22:28

You have to create your temp table outside of your select statements
and then insert into it. For example,

CREATE TABLE #tmp_table
AS SELECT *
FROM PRODUCTS
WHERE 1 = 2

IF @condition = 0
BEGIN
INSERT #tmp_table
SELECT *
FROM products ....
END
ELSE
....

Hope it helps
Teresa

das wrote:
> hello all,
> this might be simple:
>
> I populate a temp table based on a condition from another table:
>
> select @condition = condition from table1 where id=1 [this will give
> me either 0 or 1]
>
> in my stored procedure I want to do this:
>
> if @condition = 0
> begin
> select * into #tmp_table
> from products p
> inner join
> sales s on p.p_data = s.p_data
> end
> else
> begin
> select * into #tmp_table
> from products p
> left join
> sales s on p.p_data = s.p_data
> end
>
> Tha above query would not work since SQL thinks I am trying to use the
> same temp table twice.
>
> As you can see the major thing that gets effected with the condiction
> being 0/1 is the join (inner or outer). The actual SQL is much bigger
> with other joins but the only thing changing in the 2 sql's is the join
> between products and sales tables.
>
> any ideas gurus on how to use different sql's into temp table based on
> the condition?
>
> thanks
> adi

[Back to original message]