TIDB create table...select is not implemented yet

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

Original topic: TIDB create table…select is not implemented yet

| username: TiDBer_小阿飞

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.3
[Reproduction Path] CREATE TABLE
[Encountered Problem: Phenomenon and Impact] Error “TIDB create table…select is not implemented yet”
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

| username: 像风一样的男子 | Original post link

You can change it to:

create table aa like cc;
insert into aa select * from cc;
| username: MrSylar | Original post link

TiDB does not support CTAS statements.

| username: TiDBer_小阿飞 | Original post link

Is the “create table select * from” syntax not supported?

| username: 像风一样的男子 | Original post link

Not supported

| username: dba远航 | Original post link

You used an unsupported CTAS statement.

| username: tidb狂热爱好者 | Original post link

The image you provided is not accessible. Please provide the text you need translated.

| username: Jellybean | Original post link

The create table select syntax is not supported, but you can refer to the method suggested by the expert above to work around it.

One reason this syntax is not supported is due to the efficiency issues of distributed large transaction processing. Besides, for tables that are slightly larger than small tables, it is easy to encounter problems that exceed transaction limits.

The insert into select method mentioned above will also have the same issue. So how do we handle this? A better approach is to give up the atomicity and consistency of large transactions and split the large transaction into multiple smaller transactions through some means. One of these methods is to use the non-transactional features of the cluster or write scripts to complete the task.

If you need to strongly ensure transactional features, you can use other tools to accomplish this, such as using dumpling+lightning for export and import, or using spark for full table export and import.

| username: xingzhenxiang | Original post link

First create the table, then insert.

| username: andone | Original post link

There are still differences in syntax compared to MySQL, and it is not supported in terms of syntax. It is recommended to use a different approach.

| username: zhanggame1 | Original post link

It does not support “create table as”. You can use “create table like” or manually create the table and then use “insert into select” to import data. When the data volume for “insert into select” is large, it can easily cause OOM. You can adjust the “tidb_mem_quota_query” parameter, the default 1G is too small.

| username: system | Original post link

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