我確實有一個問題，如何將大量資料行從具有條件的現有表中插入到表中。所以這第一個代碼是我創建表的方式。
CREATE TABLE MARKETING_COMMODITY
AS(
SELECT A.DTIME_SIGNATURE
, A.AMT_SIGNED
, A.CNT_SIGNED
, A.APPLICATION_AMOUNT
, A.PRODUCT
, A.PRODUCT_TYPE
, A.PRODUCT_PRICE
, VSR.NAME_PRODUCER
, vsr.text_model_number
, vsp.partner_name
, vsp.partner_brand
, vspl.salesroom
, vspl.mall
FROM DM_SALES.V_SALES_DM_DATA A
LEFT JOIN DM_SALES.V_SALES_DM_PARTNER VSP ON A.CODE_SALESROOM_PARTNER = VSP.CODE_SALESROOM_PARTNER
LEFT JOIN dm_sales.v_sales_dm_pos_list vspl on a.code_salesroom = vspl.code_salesroom
LEFT JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER
WHERE 1=1
AND a.contract_state <> 'Cancelled'
AND a.cnt_signed=1
AND A.LOAN_TYPE = 'Consumer Loan'
AND (TRUNC(A.DTIME_SIGNATURE) BETWEEN DATE'2022-01-01' AND DATE'2022-08-31')
;
這就是我想插入新行的方式（就像更新表以獲取最新資料一樣）
INSERT INTO MARKETING_COMMODITY
VALUES(
SELECT A.DTIME_SIGNATURE
, A.AMT_SIGNED
, A.CNT_SIGNED
, A.APPLICATION_AMOUNT
, A.PRODUCT
, A.PRODUCT_TYPE
, A.PRODUCT_PRICE
, VSR.NAME_PRODUCER
, vsr.text_model_number
, vsp.partner_name
, vsp.partner_brand
, vspl.salesroom
, vspl.mall
FROM DM_SALES.V_SALES_DM_DATA A
LEFT JOIN DM_SALES.V_SALES_DM_PARTNER VSP ON A.CODE_SALESROOM_PARTNER = VSP.CODE_SALESROOM_PARTNER
LEFT JOIN dm_sales.v_sales_dm_pos_list vspl on a.code_salesroom = vspl.code_salesroom
LEFT JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER
WHERE 1=1
AND a.contract_state <> 'Cancelled'
AND a.cnt_signed=1
AND A.LOAN_TYPE = 'Consumer Loan'
AND (TRUNC(A.DTIME_SIGNATURE) BETWEEN DATE'2022-09-01' AND DATE'2022-09-10')
;
你有什么建議？謝謝！
uj5u.com熱心網友回復：
不要將“ CREATE TABLE AS SELECT ”的語法與“ INSERT INTO SELECT ”的語法混淆。明確添加您要插入的列并洗掉“AS”關鍵字。這應該有效：
INSERT INTO MARKETING_COMMODITY
(
dtime_signature
,amt_signed
,cnt_signed
,application_amount
,product
,product_type
,product_price
,name_producer
,text_model_number
,partner_name
,partner_brand
,salesroom
,mall
)
SELECT A.DTIME_SIGNATURE
, A.AMT_SIGNED
, A.CNT_SIGNED
, A.APPLICATION_AMOUNT
, A.PRODUCT
, A.PRODUCT_TYPE
, A.PRODUCT_PRICE
, VSR.NAME_PRODUCER
, vsr.text_model_number
, vsp.partner_name
, vsp.partner_brand
, vspl.salesroom
, vspl.mall
FROM DM_SALES.V_SALES_DM_DATA A
LEFT JOIN DM_SALES.V_SALES_DM_PARTNER VSP ON A.CODE_SALESROOM_PARTNER = VSP.CODE_SALESROOM_PARTNER
LEFT JOIN dm_sales.v_sales_dm_pos_list vspl on a.code_salesroom = vspl.code_salesroom
LEFT JOIN DM_SALES.V_SALES_DM_CONTRACT_BUNDLE VSR ON A.CONTRACT_NUMBER = VSR.CONTRACT_NUMBER
WHERE 1=1
AND a.contract_state <> 'Cancelled'
AND a.cnt_signed=1
AND A.LOAN_TYPE = 'Consumer Loan'
AND (TRUNC(A.DTIME_SIGNATURE) BETWEEN DATE'2022-09-01' AND DATE'2022-09-10')
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/508623.html
上一篇：SQL中行圖之間的依賴關系