Cross database insert select into temporary tables

An error occurred when executing the SQL command:
create temporary table tmp_tab_b_max as
select max(col1) as col1 from tab_a where group by col2;

Feature ‘Cross database insert select into temporary tables’ is not supported by MemSQL Distributed.
1 statement failed.


In above scenario (MemSQL 6.8.14), table tab_a in database db_a, and trying to create the temporary table tmp_tab_b_max in database db_b.

Any limitations on creating temporary tables in a database using tables in another database? Also, any limitations on size of the tables?

Hello Kumar

Currently, we allow the temporary table to exist on the same database as the source table. In your case, the temporary table should be created on db_a.
The only other schema from where we allow select, is from information_schema.

As you know temporary tables will vanish at the end of the user session. So, is there a real need for it to be created on a different database?

Thanks,

Temp tables don’t have a size limitation, other than available table memory.

Thanks Hanson. How do we check & manage the available table memory?

Thanks Narayanan. I am able to create temporary tables for smaller tables (few thousand rows) in another DB but not for larger tables (multi-million rows). Not sure what are the limits?

See our documentation about memory management:

Hi Kumar,

I am not sure how you are able to create the temporary table in another database. Even with MemSQL 7.0.6, I get the error when trying to do the same. Can you post your command here to verify?

memsql> use Test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

memsql> create temporary table temp1 as select * from ramesh.ram1;
ERROR 1749 (HY000): Feature ‘Cross database insert select into temporary tables’ is not supported by MemSQL Distributed.
memsql> select @@memsql_version;
±-----------------+
| @@memsql_version |
±-----------------+
| 7.0.6 |
±-----------------+
1 row in set (0.00 sec)

Thanks,

Hi Narayanan,

Hope you are doing good!

I work in a cross functional team where we often deal with multiple databases to gather data, store them in a temporary table and make the manipulations according to our business need. It will be really helpful if you can suggest any workaround / alternative for cross database insert.

Thanks,
Maney.