CODEWITHSUNDEEP
singlestore
Ron Stiffler
Ron Stiffler

Reputation: 21

MemSQL - why can't I do a cross-database insert into .. select

I'm trying to do a simple insert with a field list from a table in one database to a table in another. 

insert into db_a.target_table (field1,field2,field3) select field1,field2,field3 from db_b.source_table;

The error message seems straight-forward..

MemSQL does not support this type of query: Cross-database INSERT ... SELECT

Oddly enough, this example does work:

insert into db_a.target_table select * from db_b.source_table;

But this seems like such a common scenario. Has anyone run into a similar issue, and were you able to work around it?

Upvotes: 2

Views: 2159

Answers (1)

Joseph Victor
Joseph Victor

Reputation: 829

Unfortunately, this isn't allowed because it is difficult to keep such queries transactional; multi-statement transactions are used internally to guarantee transactionality of the single insert-select (if one partition fails (dup key or something), we want to rollback everything!). Since we don't have cross-db multi-statement transactions (yet!), we don't have cross-db insert-select (yet!).

Stay tuned for nicer solutions.

However, if you REAAALY want to do this, here is what you do. However,

PROCEED AT YOUR OWN RISK. THIS IS NOT A SUPPORTED PROCEEDURE.

But it should work.

1) On db_b, create a table with the same columns as source_table, but make the shard key SHARD().

2) On db_a, run SHOW PARTITIONS.

3) For each of those partitions, create a connection to db_a_<ordinal> on the host and port listed in SHOW PARTITIONS. Run SHOW DATABASES on that connection and you'll see some databases called db_b_<another>. Pick one, doesn't matter which. Run INSERT INTO db_b<another>.source_table SELECT * from db_a_<ordinal>.source_table.

3.5) At this point, you haven't yet written to a table you care about, but now we will. Look at db_b.source_table. Is everything correct? Is all the data there? Run SHOW CREATE TABLE and double check the shard key is SHARD KEY () (it should be in comments). Everything look good? Ok, we can proceed.

4) After you're done doing this for EVERY partition, you can do INSERT INTO db_b.target_table (cols) SELECT cols from db_b.source_table, or whatever you want.

Good luck!

Upvotes: 0

Related Questions