April 5, 2007 at 11:57 am
this is my first time here
i have 2 tables nativedocument & nativedocumentarchive
i need to get documents from nativedocument, if it is not availabe in nativedocument table, then i have to get it from the archive [nativedocumentarchive] table,...
can i do this using UNION??? will that be a good idea,....
please HELP,...
Regards,
Vidya
April 5, 2007 at 12:37 pm
Sure, but without knowing more about your data you'll need to build it yourself. You basically want to select from the archive table where there is no match in your main table, then "UNION ALL" that with a select from the main table.
If the data that is in both places is truly identical, you can just use a "UNION" without the "ALL" qualifier, as this should eliminate the duplicates. Which one is faster depends on your data, indexes, etc., so you might want to play around with it.
April 5, 2007 at 5:09 pm
They do the same thing where I work and it drives me nuts... what's the purpose of an archive? Right... to move infrequently used data out of the way for performance reasons. If you're going to ALWAYS look for the data in both places at the same time by using a UNION ALL between the two tables, you might as well put all the data in a single table so it'll be faster!
If you're going to use an archive correctly, you need to make your best guess as to where the data may be according to some date information and look there first. If such date information doesn't exist, always look in the smaller of the two tables (normally, the online table and NOT the archive) first... if it doesn't exist there, THEN check the archive.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply