Is it possible to split data from one table into 2 or more tables and have SQL do a partition on it? The goal is to have historical data in mytable_old while current data is in mytable. When queries are ran, sql can get data from both/either depending on query predicates.
For example all data older than 2011 goes into mytable_old while 2011 and newer stays in mytable.
select * from mytable where date between 12/1/2010 and 12/1/2011
will pull from both tables. Is this possible?
I would rather not use a view if possible. I want to avoid having any indexes on the old data.