By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,117 Members | 2,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,117 IT Pros & Developers. It's quick & easy.

insert into temp table based on if condition

P: n/a
das
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

Apr 12 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
This guy had the same problem:
http://www.experts-exchange.com/Data..._21594067.html

Doesn't look like they found much of a solution :(

Maybe you could try something with table variables and dynamic SQL?

Apr 12 '06 #2

P: n/a
select * into #tmp_table
from products p
left join
sales s on p.p_data = s.p_data
where s.p_data IS NOT NULL OR
@condition <> 0
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 12 '06 #3

P: n/a
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


Apr 12 '06 #4

P: n/a
Genius.

Apr 12 '06 #5

P: n/a
das
Thats a cool idea, I almost am half-bald over this problem.
but for me your solution works.

Just curious, how can we create a empty table with this statement?

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

I had to use:

SLECT * INTO #tmp_table
FROM PRODUCTS
WHERE 1 = 2

and then I used the if condition.

thanks a lot again!

Apr 12 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.