Implementing Support for "CREATE TABLE AS" Syntax

Translated
1

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

Original topic: 假如要自己实现create table as 语法支持

| username: vincentLi

[Overview] The Oracle syntax “create table as select * from a” is very convenient and was often used for table backups. How can this syntax be implemented in TiDB?

2
| username: ShawnYan | Original post link

Off-topic:
This syntax in MySQL is very inconvenient; DDL and DML are not good statements to combine.

3
| username: dba远航 | Original post link

By implementing database file copying and renaming at the underlying level, and adding information in the metadata, it can be achieved quickly and without errors. Of course, this is suitable for full replication.

4
| username: forever | Original post link

It’s unrealistic. How do you copy with conditions?

5
| username: forever | Original post link

First, create table like, then BATCH into to see if it can solve your problem.

6
| username: zhaokede | Original post link

Basically unrealistic, better wait for native support.

7
| username: TIDB-Learner | Original post link

Not considering ease of use, it doesn’t seem very difficult to implement.

8
| username: FutureDB | Original post link

It’s better to export with dumpling, then create table like, and finally import with lightning. The efficiency of batch non-transactional statements is still relatively poor.

9
| username: juecong | Original post link

You can create the table first, and then use insert into select *

10
| username: hey-hoho | Original post link

Sure, please provide the text you need translated from Chinese to English.

11
| username: forever | Original post link

This operation is rather cumbersome.

12
| username: FutureDB | Original post link

It is a bit cumbersome, but the execution efficiency is much higher than batch on, batch on is too slow.

13
| username: wwccmm858 | Original post link

Two steps:

  1. create table new_table_name like old_table_name;
  2. insert into new_table_name select * from old_table_name;
14
| username: 濱崎悟空 | Original post link

It seems we still need to take a two-step approach.

15
| username: ziptoam | Original post link

CREATE TABLE new_table AS SELECT column1, column2, function(column3) AS new_column FROM old_table WHERE condition;

16
| username: zhanggame1 | Original post link

Create a table, then insert into select * from …

17
| username: YuchongXU | Original post link

insert into

18
| username: cchouqiang | Original post link

You can only create table; then perform insert into select.

19
| username: residentevil | Original post link
  1. create table xxx like aaa;
  2. insert into xxx select * from aaa;
20
| username: TiDBer_gtlKoEAZ | Original post link

Create the table first, then insert.