Querying seperate tables with identical attributes 1

[eswhite]

Hello All,

I have two tables that are identical except that one table contains archive data and the other contains current data. The current table changes daily. At the end of each month, the current table is purged and its contents moved over into the archive table. I have a web based query engine that allows users to query either table but not both. I would like to treat both tables as one when invoking a query. Right now, I search each table individually and then combine the results of each search into one response. I essentially would like to create a temporary third table consisting of the data from both tables, query this table, and then delete it. The archive table is very large so I am not sure if this is a feasible approach with respect to performance. Does anybody know of a better way? Any suggestions would be greatly appreciated.

Thanks,
Eric

 

[chiph]

Create a view that joins both tables, and then direct your query through the view.
[tt]
CREATE VIEW vArchivePlusCurrent (ID, Code, Description)
AS SELECT ID, Code, Description FROM Archive
UNION
SELECT ID, Code, Description FROM Current
[/tt]
Chip H.

 

[eswhite]

Thanks alot Chip.

Eric