Create Table as Select - Design Target table with same structure as Source

How to create a table as per the source table structure?

We may need in daily life, to create a table derived from source. This will make the downstream table automatically modified if any column in the Source is changed.

In order to create a table as per the source table structure, use the following SQL:

Example:

 CREATE TABLE ARP_TBLS_DB.FACT_SRS_DLY_WTS_ACT_DTL
(
LOCN_NBR ,
REG_NBR ,
TRS_NBR ,
DAY_NBR ,
PRD_IRL_NBR ,
ITM_PLA_CNT ,
ITM_ACP_CNT ,
ITM_REJ_CNT ,
ITM_NRSP_CNT ,
ITM_CNC_CNT ,
WTS_RVN_DLR ,
WTS_CST_DLR ,
FILL_TRY_ID ,
PRI_TRY_LOCN_NBR ,
DATASET_ID ,
CREAT_TS ,
MOD_TS
)
AS
(
SELECT
A.LOCN_NBR,
A.REG_NBR,
A.TRS_NBR,
A.DAY_NBR,
A.PRD_IRL_NBR,
A.ITM_PLA_CNT,
A.ITM_ACP_CNT,
A.ITM_REJ_CNT,
A.ITM_NRSP_CNT,
A.ITM_CNC_CNT,
A.WTS_RVN_DLR,
A.ITM_ACP_CNT * COALESCE((
CASE
WHEN A.UN_CST = 0 THEN 0
ELSE (
CASE
WHEN A.UN_CST IS NULL
AND A.WTS_RVN_DLR <> 0
AND A.ITM_ACP_CNT <> 0 THEN B4.NATL_CST_PRC
ELSE (
CASE
WHEN A.UN_CST IS NULL
AND A.WTS_RVN_DLR = 0
AND A.ITM_ACP_CNT <> 0 THEN 0
ELSE A.UN_CST
END)
END)
END), 0) AS WTS_CST_DLR,
A.FILL_TRY_ID,
A.PRI_TRY_LOCN_NBR,
A.DATASET_ID,
A.CREAT_TS,
A.MOD_TS
FROM
FACT_SRS_DLY_WTS_ACT_DTL_NEW1 A
LEFT OUTER JOIN ARP_VW_DB.LU_SRS_PRODUCT B4
ON A.PRD_IRL_NBR = B4.PRD_IRL_NBR
) WITH DATA
PRIMARY INDEX (LOCN_NBR, REG_NBR, TRS_NBR, PRD_IRL_NBR);