---

{ “title”: “CREATE-TABLE-AS-SELECT”, “language”: “en” }

CREATE-TABLE-AS-SELECT

Name

CREATE TABLE AS SELECT

Description

This statement creates the table structure by returning the results from the Select statement and imports the data at the same time

grammar：

CREATE TABLE table_name [( column_name_list )]
opt_engine opt_partition opt_properties KW_AS query_stmt

illustrate:

• Fields of typedecimalare not currently supported
• The user needs to haveSELECTpermission for the source table andCREATEpermission for the target database
• After a table is created, data is imported. If the import fails, the table is deleted

Example

1. Using the field names in the SELECT statement

   create table `test`.`select_varchar` 
   PROPERTIES(\"replication_num\" = \"1\") 
   as select * from `test`.`varchar_table`
   

2. Custom field names (need to match the number of fields returned)

   create table `test`.`select_name`(user, testname, userstatus) 
   PROPERTIES(\"replication_num\" = \"1\") 
   as select vt.userId, vt.username, jt.status 
   from `test`.`varchar_table` vt join 
   `test`.`join_table` jt on vt.userId=jt.userId
   

Keywords

CREATE, TABLE, AS, SELECT

Best Practice