In memory tables functionality for Sqlite and other embedded databases

Question

I am looking for a functionality in which i can keep some data in the memory tables for frequent usage and the less frequent data in normal tables (on disk). This can be achieved in sqlite using the attach command, but problem is that if i have to query the two tables (in memory table and normal table) together, i have to do a union. This is a work around (i want to avoid union) but i want to know if sqlite has any built in mechanism for this purpose? Also, it would be great to know if sqlite is the best candidate for such kind of work in the embedded world?? Or are there any commercial embedded databases available that can work well in such cases. Looking forward for the answers.

Thanking you in anticipation Nadeem

Answer 1

You can create a temporary view along with the temporary and non-temporary table:

create table bla1(a int);
create temp table bla2(b int);
create temp view bla3 as select * from bla1 union select * from bla2;

Then you can select from both tables at once:

sqlite> insert into bla1 values (1);
sqlite> insert into bla2 values (2);
sqlite> select * from bla3;
1
2