How to use TiDB batch on?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 请问tidb batch on怎么使用?

| username: TiDBer_DqVy5ZbD

Pure SQL problem. For example, if I want to use batch to implement the operation insert into tableA1 select * from tableA2; to reduce memory overhead, how should the SQL be written?

| username: zhanggame1 | Original post link

Check the documentation BATCH | PingCAP Documentation Center

| username: TiDBer_DqVy5ZbD | Original post link

It seems that you need to specify a particular column in the documentation.

| username: 小龙虾爱大龙虾 | Original post link

Just specify the primary key.

| username: wangccsy | Original post link

Directly using Select * might result in a primary key conflict, right?

| username: TiDBer_DqVy5ZbD | Original post link

tableA2 is an empty table.

| username: 江湖故人 | Original post link

An empty table can also specify a primary key, but there’s no point in using an empty table as the source table.

| username: 江湖故人 | Original post link

create table ts(id int, v int, key(id));
insert into ts values (1,2),(2,3),(3,4),(4,5),(5,6);

create table td(id int, v int,t datetime(3)); 
batch on ts.id limit 1 insert into td select id,v,now(3) from ts;

my:root@192.168.15.22:4000=> select * from td;
 id | v |               t               
----+---+-------------------------------
  1 | 2 | 2024-01-04T18:06:01.665+08:00 
  2 | 3 | 2024-01-04T18:06:01.671+08:00 
  3 | 4 | 2024-01-04T18:06:01.672+08:00 
  4 | 5 | 2024-01-04T18:06:01.673+08:00 
  5 | 6 | 2024-01-04T18:06:01.674+08:00 
(5 rows)
| username: FutureDB | Original post link

Currently, the batch-on non-transactional DML is still somewhat limited. Simple DML is fine, but once the DML becomes a bit more complex, issues arise. For example, DML involving correlated subqueries and aliasing in inner and outer tables is not supported by batch-on non-transactional DML.

| username: Jellybean | Original post link

For this issue, you can directly refer to the official documentation to make the changes. If you encounter execution problems, then analyze and address them on a case-by-case basis.

| username: dba远航 | Original post link

You need to specify the primary key.

| username: zhang_2023 | Original post link

BATCH ON test.t2.id LIMIT 1 INSERT INTO t SELECT t2.id, t2.v, t3.v FROM t2 JOIN t3 ON t2.k = t3.k;
| username: 双开门变频冰箱 | Original post link

You need to specify the primary key.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.