How to best move a table (including Indexes) from one database to another

Help
1

Hi,

I need to move a table from one MemSQL database to another database.
Both databases are on the same cluster, but I don’t want to just copy the data, I also want to keep the structure exactly the same (e.g. indexes or column store key).

I could of course use ETL tools, but that seems like too much effort and too little performance compared to a native approach.

Thanks,
Christoph

2

You can use CREATE TABLE LIKE to create the same table structure, including indexes, and use INSERT SELECT to copy the data. You are correct that this should be more performant.

3

Here’s an example:

memsql> create database db1;
Query OK, 1 row affected (0.89 sec)

memsql> create database db2;
Query OK, 1 row affected (1.82 sec)

memsql> use db1;
Database changed

memsql> create table t (a int, b text, key (a, b));
Query OK, 0 rows affected (0.71 sec)

memsql> insert into t values (1, 'asdf');
Query OK, 1 row affected (0.83 sec)

memsql> use db2;
Database changed

memsql> create table t like db1.t;
Query OK, 0 rows affected (0.03 sec)

memsql> insert into db2.t select * from db1.t;
Query OK, 1 row affected (1.93 sec)
Records: 1  Duplicates: 0  Warnings: 0

memsql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` text CHARACTER SET utf8 COLLATE utf8_general_ci,
  KEY `a` (`a`,`b`)
  /*!90618 , SHARD KEY () */ 
) |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

memsql> select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 | asdf |
+------+------+
1 row in set (0.58 sec)