Create table as and create table like

Time:2021-7-28
Similarities:

Is to create a new table

difference
  • Create table as just copies the original data. In fact, it is to create a table from the query results
  • Create table like generates the same table structure as the source table, including index and primary key. The data needs to be copied in with insert into statement.

give an example:

DROP TABLE IF EXISTS tbxxx;
CREATE TABLE tbxxx LIKE tbsrc;
INSERT INTO tbxxx PARTITION (platform_id)
SELECT
...
u.created_at,
u.updated_at,
CASE WHEN n.user_id IS NULL THEN 0 ELSE 1 END AS noise,
u.platform_id
FROM tbsrc AS u
LEFT JOIN transforms.medicine_inquiry_user_noise AS n
ON u.user_id = n.user_id;