How to use a CTE to do an insertion

Question

WITH query_one AS (
  SELECT g.cust_id, 
    g.acct_crncy_code, 
    g.acid, 
    (SELECT ISNULL(SUM(ato.cr_tot), 0) FROM  [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_ATO] ato WHERE acid = g.acid AND start_date >= CONVERT(DATETIME, @startdate,10) AND end_date <= CONVERT(DATETIME, @asatdate,10)) AS sanctioned_ato,
    g.acct_ownership, 
    CASE 
      WHEN g.schm_code = 'OCALL' THEN 'TDA'
      ELSE g.schm_type
    END schm_type, 
    g.schm_code, 
    g.acct_opn_date, 
    (SELECT /*+ index(IDX_EOD_ACCT_BAL_TABLE) */ ISNULL(e.tran_date_bal,0) FROM [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_EAB] e WHERE  Convert(DATETIME, @asatdate, 10) BETWEEN e.eod_date AND e.end_eod_date AND e.acid = g.acid) AS tran_date_bal, 
    g.last_tran_date,
    g.gl_sub_head_code,
    g.sol_id, 
    Convert(varchar, @asatdate) AS rep_date 
  FROM   [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_GAM] g 
  WHERE g.entity_cre_flg = 'Y' 
    AND g.del_flg = 'N' 
    AND g.schm_type IN ( 'TDA', 'SBA', 'CAA' ) 
    AND ISNULL(g.acct_cls_date, '01-01-9999') > Convert(DATETIME, @asatdate, 10) 
    AND NOT EXISTS(SELECT 1 FROM [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_SST]  WHERE set_id = '129' AND sol_id = g.sol_id)
    AND EXISTS(SELECT 1 FROM [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_SST]  WHERE set_id = @in_sol AND sol_id = g.sol_id)
) ---SELECT * FROM  query_one WHERE  tran_date_bal > 0); 
  INSERT /*+ APPEND */ INTO [DEV_EIMDW_BOJ_REPORTS].[dbo].[boj_raw_accounts_data](CUSTOMER_ID,CURRENCY,ACID,SANCTIONED_ATO,ACCOUNT_OWNERSHIP,SCHEME_TYPE,SCHEME_CODE,ACCOUNT_OPEN_DATE,TRANSACTION_BALANCE,
  LAST_TRAN_DATE,GL_SUB_HEAD_CODE,SOL_ID,REP_DATE)
  SELECT *
  FROM query_one 
  WHERE tran_date_bal > 0;

So I'm doing a procedure that with will insert into some tables using the CTE. Currently, its not inserting anything so I'm wondering if something is wrong with the using CTE and insert, I'm not seeing the issues. The parameters for the procedure is just a date and a varchar.

Does that select statement return any rows? You should be specifying the columns in your select statement and not using *. Do you have a try/catch in your code where the code is swallowing the exception? — Sean Lange, Mar 05 '19 at 21:03
Possible duplicate of [Combining INSERT INTO and WITH/CTE](https://stackoverflow.com/questions/3306096/combining-insert-into-and-with-cte) — Igor, Mar 05 '19 at 21:04
You do know it is insane to use a `*` in an insert statement. Take the time to write the `FROM_COLUMN AS TO_COLUMN` -- then you will find your problem and delete this question in embarrassment. — Hogan, Mar 05 '19 at 21:08
Why do you need to convert parameters to datetime? If they are intended to be datetime values, you should declare them as such. You ISNULL usage is also in the wrong place. The subquery might find no rows, so it (the subquery) should be wrapped in ISNULL. — SMor, Mar 05 '19 at 21:58
And DO NOT cultivate the (bad) habit of converting to varchar without specifying a length. — SMor, Mar 05 '19 at 22:00

score 1 · Accepted Answer · answered Mar 05 '19 at 21:17

Here is how to implement your two sub queries

  FROM [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_GAM] g 
  JOIN [DEV_EIMDW_Archive].[ARCHOWN].[FINCL_SST] SST ON SST.set_id = @in_sol AND SST.sol_id = g.sol_id AND SST.set_id <> '129'
  WHERE g.entity_cre_flg = 'Y' 
    AND g.del_flg = 'N' 
    AND g.schm_type IN ( 'TDA', 'SBA', 'CAA' ) 
    AND ISNULL(g.acct_cls_date, '01-01-9999') > Convert(DATETIME, @asatdate, 10)

How to use a CTE to do an insertion

1 Answers1