best way to select data from one table or another

Question

I have two temporary tables, say #t1 and #t2 in Sql Server 2008. I need to create #t3 such as:

when #t1 has rows, indifferently of #t2's contents, #t3 = select * from #t1
when #t1 has no rows, #t3 = select * from #t2

we can assume #t1 and #t2 have the same columns but I don't think I would like to rely on that fact.

I was thinking of something that draws some logic out of 'if exists (select * ...)' statements, but wouldn't there be better like some sort of bool operators ?

How would you want to treat the case when t1 and t2 do not have the same columns? — Radu Gheorghiu, Jul 18 '13 at 16:04
@Radu Gheorghiu It doesn't matter since he's using `#temporary table`. — EmmanuelRC, Jul 18 '13 at 16:20

score 2 · Accepted Answer · answered Jul 18 '13 at 16:08

2

The simplest way is to implement the logic as:

if (exists (select * from #t1))
begin
    select *
    into #t3
    from #t1;
end;
else
begin
    select *
    into #t3
    from #t2;
end;

You can do this in one statement as:

select t.*
into #t3
 from ((select *
        from #t1
       )
       union all
       (select *
        from #t2
        where not exists(select * from #t1)
       )
      ) t

However, I think the explicit if is a clearer way to express your intent.

answered Jul 18 '13 at 16:08

Gordon Linoff

1,242,037
58
646
786

2

+1, but changing the `exists(select * from #t1)` to `exists(select 1 from #t1)` will result in a much better performance, since `1` is the lowest bit that can be read, there's no need to select all the data just to verify if anything exists. – EmmanuelRC Jul 18 '13 at 16:14
3

@EmmanuelRC the optimizer is smart enough to avoid considering the SELECT list, so your comment is wrong. See: http://stackoverflow.com/a/4115354/904178 – Efran Cobisi Jul 18 '13 at 16:19
@EfranCobisi Even better – Radu Gheorghiu Jul 18 '13 at 16:21
1

@Efran Cobisi Thanks for pointing that out, I think that I just come from the old school then, will test that though. lol – EmmanuelRC Jul 18 '13 at 16:25
it just seems that both branches are being executed when I try your first example ? I drop #t3 before the if statement, but always get an error in the second branch complaining about #t3 already existing – BuZz Jul 18 '13 at 16:41
yeah, seems like my problem is showcases right there http://stackoverflow.com/questions/16468290/using-if-else-to-determine-a-select-into-statement – BuZz Jul 18 '13 at 16:49

score 0 · Answer 2 · answered Jul 18 '13 at 16:08

This query would do just that, would see if there are rows in t1 and in that case populate table t3 with the contents of t1, provided they have the same columns, otherwise it populates t3 with the contents of t2.

IF(select count(*) from t1) > 0 
    BEGIN 
        select * into t3 from t1
    END
ELSE
    BEGIN
        select * into t3 from t2
    END

Here is a SQLFiddle so you can see how it works. To test the case when t1 does not have any rows, just remove the line that inserts into t1, rebuild schema and re-run the query.

best way to select data from one table or another

2 Answers2