Union not working when one table empty

[TonyScarpelli]

I have a stored procedure that gets records from two tables with the same structure. I want to combine them into one table, and I have set up a union for this.

However, if the first table (Tmp_VendSum) has no records in it and the other (Tmp_VendSum2) does, the returned data set is empty.

It looks like this
SELECT * FROM Tmp_VendSum
UNION
SELECT * FROM Tmp_VendSum2

Can someone explain if I am using the right syntax for what I want to do?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[gradley]

The syntax looks fine. I would look at the stored procedure to make sure nothing else is causing this.

Here a code sample using your scenario that works fine:

create table #table1
(
assoc_ID char(4) NULL
)

create table #table2
(
Assoc_ID char(4) NULL
)


insert into #table2
Select '0001'


select * 
from #table1
Union 
select * 
from #table2


drop table #table1
drop table #table2

 

[gradley]

You may want to use the "UNION ALL" statement as this will include "ALL" even duplicate records from both tables.

This is not the cause of the problem you are having though.

 

[SQLSister]

In the first place a table should not ever be empty. If it is you are doing something wrong. If this is a place to store data temporarily them you should never use a real table, use a table variable (preferred) or a temp table instead.

Next you should never ever unser any circumatnces use select * in a union statment. The statement won't work if the columns do not match up. SO all it takes to break it issomeone rearranging the column ider in one table or adding a column. More than like this is your problem that the columns don't match in number or datatype.

PLease explain what you are doing, maybe it is possible to containthe data in one place which would reduce the need for Union altogether since Union is not the most efficent thing inthe world to use.


Questions about posting. See faq183-874

 

[TonyScarpelli]

SQLSister, et all, thanks for your responses.

Here is what I am trying to do:

I have two tables in a Purchasing application, one with Archived data, and one is the current table used by everyone.

When one of my buyers wants to see archived data they usually enter a date range, and other search criteria. I check the date to see if it's in the archive table and if so, pick out all the records since that date and put it into another table (Tmp_Archive) not a #temporary table, however, just a table I drop and recreate since it's not going to be used a whole lot. The archive table and the current table both have the same structure.

If there are records in the archive table I specifically search it and the current table for all the search conditions including dates, vendor, etc., using two different select statements, into two different tables I have all mentioned, one on the archived table and one on current table.

If the results come up empty on the archived table it is empty with no records in it, yet it exists in my database because I can open it, and it has no records in it.

So, when I do the UNION, even a UNION ALL, even though the second table has records and the first one doesn't, I get an empty table.

I am just learning how to do this stuff, and I am in a testing stage here, so any suggestions on this scenario is appreciated.

So, does this explanation show anyone what I am doing wrong?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[SQLSister]

Ok using the table as you described is a poor practice. It creates unnecessary writes to the transaction table. Multiple users trying this at the same time would mess this sytem up as one could drop and replace the table before the other one was finshed using it. You should just query the data with a where clause. Or if you need the subset of data for multiple things, store it in a temp table or table variable.

As gradley showed a union with the first table empty works just fine. You could try reversing the order of the selects, but you shouldn;t need to.

Perhpas your problem is that neithe stament is actually returning any records? Have you run each select by itself to verify that at least one of them is returning records?

Questions about posting. See faq183-874