How to insert numerous rows on a table from another table with conditions on Oracle SQL-CodePudding

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!

CodePudding user response：

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') 
;