I do have a problem on how to insert numerous rows of data into a table from an existing table with conditions. So this first code is how I created my table.
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')
;
And this is how I’d like to insert my new rows (its like updating the table to get the new data up to the current day)
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')
;
What can you suggest? Thanks!
>Solution :
Don’t confuse the syntax for "CREATE TABLE AS SELECT" with the syntax for "INSERT INTO SELECT". Explicitely add the columns you’re inserting into and remove the "AS" keyword. This should work:
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')
;