Re: insert into temp table based on if condition



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

.



Relevant Pages

  • Re: Display results horizontal and not vertical
    ... drop table #Temp ... INNER JOIN Personnel AS p ... INT) ELSE 0 END) AS clicked_cnt ... using the dynamic sql mentioned below to create the sum statements. ...
    (microsoft.public.sqlserver.programming)
  • Re: how to select unique based on record count
    ... FROM #TEMP B WHERE A.Customer = B.Customer ... ORDER BY B.DESC) AS [Sales Rep] ... SQL Server Programmer ... > I have a file which has duplicate key values but I need an SQL statement ...
    (microsoft.public.sqlserver.programming)
  • insert into temp table based on if condition
    ... I populate a temp table based on a condition from another table: ... sales s on p.p_data = s.p_data ... Tha above query would not work since SQL thinks I am trying to use the ...
    (comp.databases.ms-sqlserver)
  • Re: MAX vs. LAST
    ... CONVERT, Null) As LASTofPayterms ... INTO #temp FROM sales ... UPDATE #temp SET ... >> implementation, of a ROW aggregate ...
    (microsoft.public.access.queries)
  • Re: How to Create Local Temporary Table
    ... As Daniel pointed out, many times, it is locking that forces one to look at temp tables and Oracle does not share this problem. ... With the improvements in Oracle 9i and 10g and what Oracle has added to its SQL feature set, one can most often get by with just a single SQL statement, which is more efficient than writing data to a table and then reading it back again. ... If it places the group's minds at ease DB2 supports neither VARRAY nor local temps. ...
    (comp.databases.oracle.server)