How to use insert_all for auto_increment column

SingleStore Development
#1

I have one table which has auto_increment column and in procedure we are looping and performing one by one insert but now observing delay in response, could you pls share example for insert_all with auto_increment column.(I tried but not working with me )

Thank you…

#2

It is not possible to use insert_all if the target table has an auto_increment column, and you want to auto-fill the auto_increment column.

You could try inserting the data in bulk to a scratch or temp table and then use an INSERT…SELECT… to move data from that table to the final target table (the one with the auto_increment). That might be faster than doing a bunch of singleton inserts, depending on the scenario.

Here’s what I had in mind re: the INSERT…SELECT… approach.

memsql> create table t(a int auto_increment, b int, key(a));
Query OK, 0 rows affected, 1 warning (0.18 sec)
memsql> create table s(b int);
Query OK, 0 rows affected (0.09 sec)
memsql> insert s values(100),(200);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
memsql> insert into t(b) select b from s;
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0
memsql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |  100 |
| 2 |  200 |
+---+------+