时间戳

首页 > 解决方案 > 如何使用 CTE 进行插入

sql-server - 如何使用 CTE 进行插入

问题描述

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;

所以我正在做一个程序，它将使用 CTE 插入到一些表中。目前，它没有插入任何东西，所以我想知道使用 CTE 和插入是否有问题，我没有看到问题。该过程的参数只是一个日期和一个 varchar。

标签： sql-server

解决方案

以下是如何实现您的两个子查询

  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)

推荐阅读