Cross database insert select into temporary tables

Help
1

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?

2

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,

3

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

4

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

5

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?

6

See our documentation about memory management:

7

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,

8

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.