USE Diablo
GO
DROP PROCEDURE Template.LoadTransPivotedTableCreate_4
GO
CREATE PROCEDURE Template.LoadTransPivotedTableCreate_4
@DataFolder VARCHAR(30),
@PrintSQL BIT = 0,
@ExecSQL BIT = 1,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
DECLARE @DBNAME VARCHAR(MAX) = DB_NAME()
SET @SQL = 'IF OBJECT_ID(''DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted'') IS NOT NULL
DROP TABLE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted
CREATE TABLE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted (DataSupplierId SMALLINT NOT NULL,
T_BATCH_DT INT NOT NULL,
T_BATCH_SEQ INT NOT NULL,
Count1 TINYINT NOT NULL,
Count1Max_bld_comment_txt TINYINT NULL,
Count1Max_buy_addr TINYINT NULL,
Count1Max_buy_addr_dpvnote TINYINT NULL,
Count1Max_buy_cd_tabl_grp TINYINT NULL,
Count1Max_buy_co_nm TINYINT NULL,
Count1Max_buy_grp_info_01 TINYINT NULL,
Count1Max_buy_nm_grp TINYINT NULL,
Count1Max_buy_orig_addr TINYINT NULL,
Count1Max_buy_orig_nm TINYINT NULL,
Count1Max_comment_txt TINYINT NULL,
Count1Max_deed_sec_cat_cd TINYINT NULL,
Count1Max_dist_tax_grp TINYINT NULL,
Count1Max_lot_info_grp TINYINT NULL,
Count1Max_mtg_grp_info_01 TINYINT NULL,
Count1Max_mtg_grp_info_01_comment TINYINT NULL,
Count1Max_mtg_grp_info_01_int_rt TINYINT NULL,
Count1Max_mtg_grp_info_01_legal TINYINT NULL,
Count1Max_mtg_grp_info_01_legal_addr TINYINT NULL,
Count1Max_mtg_grp_info_02 TINYINT NULL,
Count1Max_prin_addr TINYINT NULL,
Count1Max_sel_addr TINYINT NULL,
Count1Max_sel_cd_tabl_grp TINYINT NULL,
Count1Max_sel_co_nm TINYINT NULL,
Count1Max_sel_grp_info_01 TINYINT NULL,
Count1Max_sel_nm_grp TINYINT NULL,
Count1Max_sel_orig_addr TINYINT NULL,
Count1Max_sel_orig_nm TINYINT NULL,
Count1Max_sit_dpvnote TINYINT NULL,
Count1Max_sit_grp_info_01 TINYINT NULL,
Count1Max_sls_comment_txt TINYINT NULL,
Count1Max_sls_legal TINYINT NULL,
Count1Max_sls_notes_txt TINYINT NULL,
Count1Max_sls_reject_grp_info_01 TINYINT NULL,
Count1Max_sq_ft_grp_info_01 TINYINT NULL,
Count1Max_tax_exmp_grp_info_01 TINYINT NULL,
T_ARM_TYP CHAR(4) NULL,
T_BLD_COMMENT_TXT VARCHAR(80) NULL,
T_BUY_ADDR VARCHAR(60) NULL,
T_BUY_ADDR_DPVNOTE CHAR(3) NULL,
T_BUY_CD VARCHAR(10) NULL,
T_BUY_CD_TABL_IND CHAR(1) NULL,
T_BUY_CO_NM VARCHAR(60) NULL,
T_BUY_CORP_IND CHAR(1) NULL,
T_BUY_ETAL_IND CHAR(1) NULL,
T_BUY_FRST_NM VARCHAR(32) NULL,
T_BUY_LST_NM VARCHAR(30) NULL,
T_BUY_MI_NM VARCHAR(12) NULL,
T_BUY_NM VARCHAR(60) NULL,
T_BUY_NM_PCT SMALLINT NULL,
T_BUY_NM_TRUST_NBR VARCHAR(15) NULL,
T_BUY_ORIG_ADDR VARCHAR(60) NULL,
T_BUY_ORIG_NM VARCHAR(60) NULL,
T_BUY_OWNSHPRTS_CD CHAR(3) NULL,
T_BUY_REL_TYP_CD CHAR(2) NULL,
T_BUY_SFX_ID CHAR(4) NULL,
T_COMMENT_TXT VARCHAR(80) NULL,
T_DEED_SEC_CAT_CD CHAR(2) NULL,
T_DIST_CNTY_CD VARCHAR(20) NULL,
T_INT_RT_CHG_DT INT NULL,
T_INT_RT_CHG_FREQ CHAR(1) NULL,
T_INT_RT_CHG_INTVL TINYINT NULL,
T_INT_RT_CHG_PCT NUMERIC(6, 4) NULL,
T_INT_RT_CHG_PCTLM NUMERIC(6, 4) NULL,
T_INT_RT_INDEX_TYP CHAR(3) NULL,
T_INT_RT_PCT_MAX NUMERIC(6, 4) NULL,
T_LNDR_ADDR_APTNBR VARCHAR(6) NULL,
T_LNDR_ADDR_CARRT CHAR(4) NULL,
T_LNDR_ADDR_CITY VARCHAR(40) NULL,
T_LNDR_ADDR_CNTRY VARCHAR(30) NULL,
T_LNDR_ADDR_CSZID VARCHAR(60) NULL,
T_LNDR_ADDR_DIR_CD CHAR(2) NULL,
T_LNDR_ADDR_HSE1 VARCHAR(10) NULL,
T_LNDR_ADDR_HSE2 VARCHAR(10) NULL,
T_LNDR_ADDR_MODE CHAR(5) NULL,
T_LNDR_ADDR_PFX1 CHAR(5) NULL,
T_LNDR_ADDR_QDRNT CHAR(2) NULL,
T_LNDR_ADDR_SFX1 VARCHAR(10) NULL,
T_LNDR_ADDR_SFX2 VARCHAR(10) NULL,
T_LNDR_ADDR_ST_CD CHAR(2) NULL,
T_LNDR_ADDR_STRTNM VARCHAR(30) NULL,
T_LNDR_ADDR_ZIP_CD VARCHAR(9) NULL,
T_LNDR_ADDR1 VARCHAR(60) NULL,
T_LNDR_ADDR2 VARCHAR(60) NULL,
T_LNDR_ADDR3 VARCHAR(60) NULL,
T_LNDR_ADDR4 VARCHAR(60) NULL,
T_LNDR_AKA_NM VARCHAR(60) NULL,
T_LNDR_CENS_ID VARCHAR(10) NULL,
T_LNDR_CENTROID CHAR(4) NULL,
T_LNDR_CO_NM1 VARCHAR(60) NULL,
T_LNDR_CORP1_IND CHAR(1) NULL,
T_LNDR_CORP2_IND CHAR(1) NULL,
T_LNDR_DBA_NM VARCHAR(60) NULL,
T_LNDR_DPID_CD CHAR(2) NULL,
T_LNDR_ETAL1_IND CHAR(1) NULL,
T_LNDR_FRST_NM1 VARCHAR(60) NULL,
T_LNDR_LAT_DEGR NUMERIC(8, 6) NULL,
T_LNDR_LONG_DEGR NUMERIC(9, 6) NULL,
T_LNDR_LST_NM1 VARCHAR(60) NULL,
T_LNDR_MATCH_CD CHAR(4) NULL,
T_LNDR_NM_CHNG_IND CHAR(1) NULL,
T_LNDR_NM1 VARCHAR(60) NULL,
T_LNDR_OWNSHP1_CD CHAR(3) NULL,
T_LNDR_PHONE_NBR VARCHAR(10) NULL,
T_LNDR_REL_TP1_CD CHAR(2) NULL,
T_LOAN_NUMBER VARCHAR(20) NULL,
T_LOT_ID CHAR(5) NULL,
T_LOT_SFX_CD CHAR(1) NULL,
T_MOD_DUE_DT INT NULL,
T_MOD_INT_RT_PCT NUMERIC(6, 4) NULL,
T_MOD_MTG_AMT NUMERIC(13, 2) NULL,
T_MTG_AMT NUMERIC(13, 2) NULL,
T_MTG_ASSMPTN_AMT BIGINT NULL,
T_MTG_ASSMPTN_IND CHAR(1) NULL,
T_MTG_BLANKET_IND CHAR(1) NULL,
T_MTG_CHANGE_AMT BIGINT NULL,
T_MTG_CNSTR_LN_IND CHAR(1) NULL,
T_MTG_COMMENT_TXT1 VARCHAR(80) NULL,
T_MTG_COMMENT_TXT2 VARCHAR(80) NULL,
T_MTG_COMMENT_TXT3 VARCHAR(80) NULL,
T_MTG_COMPANY_CD VARCHAR(10) NULL,
T_MTG_CONCR_JR_IND CHAR(1) NULL,
T_MTG_DEED_CD VARCHAR(6) NULL,
T_MTG_DOC_NBR VARCHAR(12) NULL,
T_MTG_DOC_YY_DT SMALLINT NULL,
T_MTG_DT INT NULL,
T_MTG_DUE_DT INT NULL,
T_MTG_HLD_BY_CD CHAR(1) NULL,
T_MTG_INT_RT_CAP NUMERIC(6, 4) NULL,
T_MTG_INT_RT_PCT NUMERIC(6, 4) NULL,
T_MTG_INT_RT_TYP CHAR(3) NULL,
T_MTG_LIEN_POS SMALLINT NULL,
T_MTG_LKBCK_DAYS CHAR(2) NULL,
T_MTG_LOAN_TYP_CD CHAR(5) NULL,
T_MTG_MST_RCNT_IND CHAR(1) NULL,
T_MTG_PAYOFF_DT INT NULL,
T_MTG_PAYOFF_TYP CHAR(1) NULL,
T_MTG_PMT_CHNG_DT VARCHAR(8) NULL,
T_MTG_PPAY_EXP_DT VARCHAR(80) NULL,
T_MTG_PPAY_IND CHAR(1) NULL,
T_MTG_PURPOSE_TYP CHAR(1) NULL,
T_MTG_RCDED_DT INT NULL,
T_MTG_REC_BKPG_NBR VARCHAR(12) NULL,
T_MTG_RLSE_DT INT NULL,
T_MTG_SLNT_2ND_IND CHAR(1) NULL,
T_MTG_STAT_IND CHAR(1) NULL,
T_MTG_STD_CO_CD CHAR(5) NULL,
T_MTG_SUBORD_TYP CHAR(3) NULL,
T_MTG_TERM_AMT INT NULL,
T_MTG_TERM_CD CHAR(4) NULL,
T_MTG_TYPE_IND CHAR(1) NULL,
T_MTG_UPSELL_IND CHAR(1) NULL,
T_PRIN_ADDR VARCHAR(60) NULL,
T_SEL_ADDR VARCHAR(60) NULL,
T_SEL_CD VARCHAR(10) NULL,
T_SEL_CD_TABL_IND CHAR(1) NULL,
T_SEL_CO_NM VARCHAR(60) NULL,
T_SEL_CORP_IND CHAR(1) NULL,
T_SEL_ETAL_IND CHAR(1) NULL,
T_SEL_FRST_NM VARCHAR(32) NULL,
T_SEL_LST_NM VARCHAR(30) NULL,
T_SEL_MI_NM VARCHAR(12) NULL,
T_SEL_NM VARCHAR(60) NULL,
T_SEL_NM_PCT SMALLINT NULL,
T_SEL_NM_TRUST_NBR VARCHAR(15) NULL,
T_SEL_ORIG_ADDR VARCHAR(60) NULL,
T_SEL_ORIG_NM VARCHAR(60) NULL,
T_SEL_OWNSHPRTS_CD CHAR(3) NULL,
T_SEL_REL_TYP_CD CHAR(2) NULL,
T_SEL_SFX_ID CHAR(4) NULL,
T_SIT_APT_NBR VARCHAR(6) NULL,
T_SIT_CITY_NM VARCHAR(40) NULL,
T_SIT_CSZTYP_IND CHAR(1) NULL,
T_SIT_DIR_CD_L CHAR(2) NULL,
T_SIT_DPVNOTE CHAR(3) NULL,
T_SIT_HSE1_NBR VARCHAR(10) NULL,
T_SIT_HSE2_NBR VARCHAR(10) NULL,
T_SIT_MODE_CD CHAR(5) NULL,
T_SIT_ORIG1_TXT VARCHAR(60) NULL,
T_SIT_ORIG2_TXT VARCHAR(60) NULL,
T_SIT_ORIG3_TXT VARCHAR(60) NULL,
T_SIT_PFX1_CD CHAR(5) NULL,
T_SIT_QDRNT_CD CHAR(2) NULL,
T_SIT_SFX1_CD VARCHAR(10) NULL,
T_SIT_SFX2_CD VARCHAR(10) NULL,
T_SIT_ST_CD CHAR(2) NULL,
T_SIT_STRT_NM VARCHAR(30) NULL,
T_SIT_STRTNM_PFX VARCHAR(20) NULL,
T_SIT_TYP_IND CHAR(1) NULL,
T_SIT_ZIP_CD VARCHAR(9) NULL,
T_SLS_COMMENT_TXT VARCHAR(80) NULL,
T_SLS_LEGAL VARCHAR(250) NULL,
T_SLS_NOTES_TXT VARCHAR(80) NULL,
T_SLS_REJECT_CD CHAR(3) NULL,
T_SLS_REJECT_OVRD_ID VARCHAR(8) NULL,
T_SQ_FT_DEPTH_NBR NUMERIC(9, 2) NULL,
T_SQ_FT_FRONT_NBR NUMERIC(9, 2) NULL,
T_SQ_FT_NBR NUMERIC(12, 3) NULL,
T_UNV_MTG_LN_TP_CD CHAR(5) NULL,
T_UNV_TAX_EXMP_CD CHAR(3) NULL,
T_VAR_RIDER_IND CHAR(2) NULL)
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_deed_sec_cat_cd AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
INSERT DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK)
(DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_deed_sec_cat_cd,
T_DEED_SEC_CAT_CD)
SELECT
s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_DEED_SEC_CAT_CD
FROM s
CREATE UNIQUE CLUSTERED INDEX trans_pivoted ON DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted (DataSupplierId, T_BATCH_DT, T_BATCH_SEQ, Count1) WITH (DATA_COMPRESSION = ROW)
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sit_grp_info_01 AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sit_grp_info_01 = s.Count1Max,
t.T_SIT_APT_NBR = s.T_SIT_APT_NBR,
t.T_SIT_CITY_NM = s.T_SIT_CITY_NM,
t.T_SIT_CSZTYP_IND = s.T_SIT_CSZTYP_IND,
t.T_SIT_DIR_CD_L = s.T_SIT_DIR_CD_L,
t.T_SIT_HSE1_NBR = s.T_SIT_HSE1_NBR,
t.T_SIT_HSE2_NBR = s.T_SIT_HSE2_NBR,
t.T_SIT_MODE_CD = s.T_SIT_MODE_CD,
t.T_SIT_ORIG1_TXT = s.T_SIT_ORIG1_TXT,
t.T_SIT_ORIG2_TXT = s.T_SIT_ORIG2_TXT,
t.T_SIT_ORIG3_TXT = s.T_SIT_ORIG3_TXT,
t.T_SIT_PFX1_CD = s.T_SIT_PFX1_CD,
t.T_SIT_QDRNT_CD = s.T_SIT_QDRNT_CD,
t.T_SIT_SFX1_CD = s.T_SIT_SFX1_CD,
t.T_SIT_SFX2_CD = s.T_SIT_SFX2_CD,
t.T_SIT_ST_CD = s.T_SIT_ST_CD,
t.T_SIT_STRT_NM = s.T_SIT_STRT_NM,
t.T_SIT_STRTNM_PFX = s.T_SIT_STRTNM_PFX,
t.T_SIT_TYP_IND = s.T_SIT_TYP_IND,
t.T_SIT_ZIP_CD = s.T_SIT_ZIP_CD
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sit_grp_info_01,
T_SIT_APT_NBR,
T_SIT_CITY_NM,
T_SIT_CSZTYP_IND,
T_SIT_DIR_CD_L,
T_SIT_HSE1_NBR,
T_SIT_HSE2_NBR,
T_SIT_MODE_CD,
T_SIT_ORIG1_TXT,
T_SIT_ORIG2_TXT,
T_SIT_ORIG3_TXT,
T_SIT_PFX1_CD,
T_SIT_QDRNT_CD,
T_SIT_SFX1_CD,
T_SIT_SFX2_CD,
T_SIT_ST_CD,
T_SIT_STRT_NM,
T_SIT_STRTNM_PFX,
T_SIT_TYP_IND,
T_SIT_ZIP_CD)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SIT_APT_NBR,
s.T_SIT_CITY_NM,
s.T_SIT_CSZTYP_IND,
s.T_SIT_DIR_CD_L,
s.T_SIT_HSE1_NBR,
s.T_SIT_HSE2_NBR,
s.T_SIT_MODE_CD,
s.T_SIT_ORIG1_TXT,
s.T_SIT_ORIG2_TXT,
s.T_SIT_ORIG3_TXT,
s.T_SIT_PFX1_CD,
s.T_SIT_QDRNT_CD,
s.T_SIT_SFX1_CD,
s.T_SIT_SFX2_CD,
s.T_SIT_ST_CD,
s.T_SIT_STRT_NM,
s.T_SIT_STRTNM_PFX,
s.T_SIT_TYP_IND,
s.T_SIT_ZIP_CD);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_buy_nm_grp AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_buy_nm_grp = s.Count1Max,
t.T_BUY_CD = s.T_BUY_CD,
t.T_BUY_CORP_IND = s.T_BUY_CORP_IND,
t.T_BUY_ETAL_IND = s.T_BUY_ETAL_IND,
t.T_BUY_NM = s.T_BUY_NM,
t.T_BUY_NM_PCT = s.T_BUY_NM_PCT,
t.T_BUY_NM_TRUST_NBR = s.T_BUY_NM_TRUST_NBR,
t.T_BUY_OWNSHPRTS_CD = s.T_BUY_OWNSHPRTS_CD,
t.T_BUY_REL_TYP_CD = s.T_BUY_REL_TYP_CD
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_buy_nm_grp,
T_BUY_CD,
T_BUY_CORP_IND,
T_BUY_ETAL_IND,
T_BUY_NM,
T_BUY_NM_PCT,
T_BUY_NM_TRUST_NBR,
T_BUY_OWNSHPRTS_CD,
T_BUY_REL_TYP_CD)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BUY_CD,
s.T_BUY_CORP_IND,
s.T_BUY_ETAL_IND,
s.T_BUY_NM,
s.T_BUY_NM_PCT,
s.T_BUY_NM_TRUST_NBR,
s.T_BUY_OWNSHPRTS_CD,
s.T_BUY_REL_TYP_CD);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_buy_addr AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_buy_addr = s.Count1Max,
t.T_BUY_ADDR = s.T_BUY_ADDR
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_buy_addr,
T_BUY_ADDR)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BUY_ADDR);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_lot_info_grp AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_lot_info_grp = s.Count1Max,
t.T_LOT_ID = s.T_LOT_ID,
t.T_LOT_SFX_CD = s.T_LOT_SFX_CD
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_lot_info_grp,
T_LOT_ID,
T_LOT_SFX_CD)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_LOT_ID,
s.T_LOT_SFX_CD);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_buy_grp_info_01 AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_buy_grp_info_01 = s.Count1Max,
t.T_BUY_FRST_NM = s.T_BUY_FRST_NM,
t.T_BUY_LST_NM = s.T_BUY_LST_NM,
t.T_BUY_MI_NM = s.T_BUY_MI_NM,
t.T_BUY_SFX_ID = s.T_BUY_SFX_ID
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_buy_grp_info_01,
T_BUY_FRST_NM,
T_BUY_LST_NM,
T_BUY_MI_NM,
T_BUY_SFX_ID)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BUY_FRST_NM,
s.T_BUY_LST_NM,
s.T_BUY_MI_NM,
s.T_BUY_SFX_ID);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_buy_orig_nm AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_buy_orig_nm = s.Count1Max,
t.T_BUY_ORIG_NM = s.T_BUY_ORIG_NM
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_buy_orig_nm,
T_BUY_ORIG_NM)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BUY_ORIG_NM);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_mtg_grp_info_01 AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_mtg_grp_info_01 = s.Count1Max,
t.T_ARM_TYP = s.T_ARM_TYP,
t.T_LOAN_NUMBER = s.T_LOAN_NUMBER,
t.T_MTG_AMT = s.T_MTG_AMT,
t.T_MTG_ASSMPTN_AMT = s.T_MTG_ASSMPTN_AMT,
t.T_MTG_ASSMPTN_IND = s.T_MTG_ASSMPTN_IND,
t.T_MTG_BLANKET_IND = s.T_MTG_BLANKET_IND,
t.T_MTG_CNSTR_LN_IND = s.T_MTG_CNSTR_LN_IND,
t.T_MTG_DEED_CD = s.T_MTG_DEED_CD,
t.T_MTG_DOC_NBR = s.T_MTG_DOC_NBR,
t.T_MTG_DOC_YY_DT = s.T_MTG_DOC_YY_DT,
t.T_MTG_DT = s.T_MTG_DT,
t.T_MTG_DUE_DT = s.T_MTG_DUE_DT,
t.T_MTG_HLD_BY_CD = s.T_MTG_HLD_BY_CD,
t.T_MTG_INT_RT_CAP = s.T_MTG_INT_RT_CAP,
t.T_MTG_INT_RT_PCT = s.T_MTG_INT_RT_PCT,
t.T_MTG_INT_RT_TYP = s.T_MTG_INT_RT_TYP,
t.T_MTG_LIEN_POS = s.T_MTG_LIEN_POS,
t.T_MTG_LKBCK_DAYS = s.T_MTG_LKBCK_DAYS,
t.T_MTG_LOAN_TYP_CD = s.T_MTG_LOAN_TYP_CD,
t.T_MTG_MST_RCNT_IND = s.T_MTG_MST_RCNT_IND,
t.T_MTG_PMT_CHNG_DT = s.T_MTG_PMT_CHNG_DT,
t.T_MTG_PPAY_EXP_DT = s.T_MTG_PPAY_EXP_DT,
t.T_MTG_PPAY_IND = s.T_MTG_PPAY_IND,
t.T_MTG_PURPOSE_TYP = s.T_MTG_PURPOSE_TYP,
t.T_MTG_RCDED_DT = s.T_MTG_RCDED_DT,
t.T_MTG_REC_BKPG_NBR = s.T_MTG_REC_BKPG_NBR,
t.T_MTG_STAT_IND = s.T_MTG_STAT_IND,
t.T_MTG_STD_CO_CD = s.T_MTG_STD_CO_CD,
t.T_MTG_SUBORD_TYP = s.T_MTG_SUBORD_TYP,
t.T_MTG_TERM_AMT = s.T_MTG_TERM_AMT,
t.T_MTG_TERM_CD = s.T_MTG_TERM_CD,
t.T_MTG_TYPE_IND = s.T_MTG_TYPE_IND,
t.T_MTG_UPSELL_IND = s.T_MTG_UPSELL_IND,
t.T_UNV_MTG_LN_TP_CD = s.T_UNV_MTG_LN_TP_CD,
t.T_VAR_RIDER_IND = s.T_VAR_RIDER_IND
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_mtg_grp_info_01,
T_ARM_TYP,
T_LOAN_NUMBER,
T_MTG_AMT,
T_MTG_ASSMPTN_AMT,
T_MTG_ASSMPTN_IND,
T_MTG_BLANKET_IND,
T_MTG_CNSTR_LN_IND,
T_MTG_DEED_CD,
T_MTG_DOC_NBR,
T_MTG_DOC_YY_DT,
T_MTG_DT,
T_MTG_DUE_DT,
T_MTG_HLD_BY_CD,
T_MTG_INT_RT_CAP,
T_MTG_INT_RT_PCT,
T_MTG_INT_RT_TYP,
T_MTG_LIEN_POS,
T_MTG_LKBCK_DAYS,
T_MTG_LOAN_TYP_CD,
T_MTG_MST_RCNT_IND,
T_MTG_PMT_CHNG_DT,
T_MTG_PPAY_EXP_DT,
T_MTG_PPAY_IND,
T_MTG_PURPOSE_TYP,
T_MTG_RCDED_DT,
T_MTG_REC_BKPG_NBR,
T_MTG_STAT_IND,
T_MTG_STD_CO_CD,
T_MTG_SUBORD_TYP,
T_MTG_TERM_AMT,
T_MTG_TERM_CD,
T_MTG_TYPE_IND,
T_MTG_UPSELL_IND,
T_UNV_MTG_LN_TP_CD,
T_VAR_RIDER_IND)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_ARM_TYP,
s.T_LOAN_NUMBER,
s.T_MTG_AMT,
s.T_MTG_ASSMPTN_AMT,
s.T_MTG_ASSMPTN_IND,
s.T_MTG_BLANKET_IND,
s.T_MTG_CNSTR_LN_IND,
s.T_MTG_DEED_CD,
s.T_MTG_DOC_NBR,
s.T_MTG_DOC_YY_DT,
s.T_MTG_DT,
s.T_MTG_DUE_DT,
s.T_MTG_HLD_BY_CD,
s.T_MTG_INT_RT_CAP,
s.T_MTG_INT_RT_PCT,
s.T_MTG_INT_RT_TYP,
s.T_MTG_LIEN_POS,
s.T_MTG_LKBCK_DAYS,
s.T_MTG_LOAN_TYP_CD,
s.T_MTG_MST_RCNT_IND,
s.T_MTG_PMT_CHNG_DT,
s.T_MTG_PPAY_EXP_DT,
s.T_MTG_PPAY_IND,
s.T_MTG_PURPOSE_TYP,
s.T_MTG_RCDED_DT,
s.T_MTG_REC_BKPG_NBR,
s.T_MTG_STAT_IND,
s.T_MTG_STD_CO_CD,
s.T_MTG_SUBORD_TYP,
s.T_MTG_TERM_AMT,
s.T_MTG_TERM_CD,
s.T_MTG_TYPE_IND,
s.T_MTG_UPSELL_IND,
s.T_UNV_MTG_LN_TP_CD,
s.T_VAR_RIDER_IND);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_mtg_grp_info_01_legal AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_mtg_grp_info_01_legal = s.Count1Max,
t.T_LNDR_AKA_NM = s.T_LNDR_AKA_NM,
t.T_LNDR_CENS_ID = s.T_LNDR_CENS_ID,
t.T_LNDR_CENTROID = s.T_LNDR_CENTROID,
t.T_LNDR_CO_NM1 = s.T_LNDR_CO_NM1,
t.T_LNDR_CORP1_IND = s.T_LNDR_CORP1_IND,
t.T_LNDR_CORP2_IND = s.T_LNDR_CORP2_IND,
t.T_LNDR_DBA_NM = s.T_LNDR_DBA_NM,
t.T_LNDR_DPID_CD = s.T_LNDR_DPID_CD,
t.T_LNDR_ETAL1_IND = s.T_LNDR_ETAL1_IND,
t.T_LNDR_FRST_NM1 = s.T_LNDR_FRST_NM1,
t.T_LNDR_LAT_DEGR = s.T_LNDR_LAT_DEGR,
t.T_LNDR_LONG_DEGR = s.T_LNDR_LONG_DEGR,
t.T_LNDR_LST_NM1 = s.T_LNDR_LST_NM1,
t.T_LNDR_MATCH_CD = s.T_LNDR_MATCH_CD,
t.T_LNDR_NM_CHNG_IND = s.T_LNDR_NM_CHNG_IND,
t.T_LNDR_NM1 = s.T_LNDR_NM1,
t.T_LNDR_OWNSHP1_CD = s.T_LNDR_OWNSHP1_CD,
t.T_LNDR_PHONE_NBR = s.T_LNDR_PHONE_NBR,
t.T_LNDR_REL_TP1_CD = s.T_LNDR_REL_TP1_CD,
t.T_MTG_COMPANY_CD = s.T_MTG_COMPANY_CD
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_mtg_grp_info_01_legal,
T_LNDR_AKA_NM,
T_LNDR_CENS_ID,
T_LNDR_CENTROID,
T_LNDR_CO_NM1,
T_LNDR_CORP1_IND,
T_LNDR_CORP2_IND,
T_LNDR_DBA_NM,
T_LNDR_DPID_CD,
T_LNDR_ETAL1_IND,
T_LNDR_FRST_NM1,
T_LNDR_LAT_DEGR,
T_LNDR_LONG_DEGR,
T_LNDR_LST_NM1,
T_LNDR_MATCH_CD,
T_LNDR_NM_CHNG_IND,
T_LNDR_NM1,
T_LNDR_OWNSHP1_CD,
T_LNDR_PHONE_NBR,
T_LNDR_REL_TP1_CD,
T_MTG_COMPANY_CD)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_LNDR_AKA_NM,
s.T_LNDR_CENS_ID,
s.T_LNDR_CENTROID,
s.T_LNDR_CO_NM1,
s.T_LNDR_CORP1_IND,
s.T_LNDR_CORP2_IND,
s.T_LNDR_DBA_NM,
s.T_LNDR_DPID_CD,
s.T_LNDR_ETAL1_IND,
s.T_LNDR_FRST_NM1,
s.T_LNDR_LAT_DEGR,
s.T_LNDR_LONG_DEGR,
s.T_LNDR_LST_NM1,
s.T_LNDR_MATCH_CD,
s.T_LNDR_NM_CHNG_IND,
s.T_LNDR_NM1,
s.T_LNDR_OWNSHP1_CD,
s.T_LNDR_PHONE_NBR,
s.T_LNDR_REL_TP1_CD,
s.T_MTG_COMPANY_CD);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sel_nm_grp AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sel_nm_grp = s.Count1Max,
t.T_SEL_CD = s.T_SEL_CD,
t.T_SEL_CORP_IND = s.T_SEL_CORP_IND,
t.T_SEL_ETAL_IND = s.T_SEL_ETAL_IND,
t.T_SEL_NM = s.T_SEL_NM,
t.T_SEL_NM_PCT = s.T_SEL_NM_PCT,
t.T_SEL_NM_TRUST_NBR = s.T_SEL_NM_TRUST_NBR,
t.T_SEL_OWNSHPRTS_CD = s.T_SEL_OWNSHPRTS_CD,
t.T_SEL_REL_TYP_CD = s.T_SEL_REL_TYP_CD
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sel_nm_grp,
T_SEL_CD,
T_SEL_CORP_IND,
T_SEL_ETAL_IND,
T_SEL_NM,
T_SEL_NM_PCT,
T_SEL_NM_TRUST_NBR,
T_SEL_OWNSHPRTS_CD,
T_SEL_REL_TYP_CD)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SEL_CD,
s.T_SEL_CORP_IND,
s.T_SEL_ETAL_IND,
s.T_SEL_NM,
s.T_SEL_NM_PCT,
s.T_SEL_NM_TRUST_NBR,
s.T_SEL_OWNSHPRTS_CD,
s.T_SEL_REL_TYP_CD);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sel_orig_nm AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sel_orig_nm = s.Count1Max,
t.T_SEL_ORIG_NM = s.T_SEL_ORIG_NM
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sel_orig_nm,
T_SEL_ORIG_NM)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SEL_ORIG_NM);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sls_reject_grp_info_01 AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sls_reject_grp_info_01 = s.Count1Max,
t.T_SLS_REJECT_CD = s.T_SLS_REJECT_CD,
t.T_SLS_REJECT_OVRD_ID = s.T_SLS_REJECT_OVRD_ID
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sls_reject_grp_info_01,
T_SLS_REJECT_CD,
T_SLS_REJECT_OVRD_ID)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SLS_REJECT_CD,
s.T_SLS_REJECT_OVRD_ID);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_comment_txt AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_comment_txt = s.Count1Max,
t.T_COMMENT_TXT = s.T_COMMENT_TXT
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_comment_txt,
T_COMMENT_TXT)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_COMMENT_TXT);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_mtg_grp_info_01_legal_addr AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_mtg_grp_info_01_legal_addr = s.Count1Max,
t.T_LNDR_ADDR_APTNBR = s.T_LNDR_ADDR_APTNBR,
t.T_LNDR_ADDR_CARRT = s.T_LNDR_ADDR_CARRT,
t.T_LNDR_ADDR_CITY = s.T_LNDR_ADDR_CITY,
t.T_LNDR_ADDR_CNTRY = s.T_LNDR_ADDR_CNTRY,
t.T_LNDR_ADDR_CSZID = s.T_LNDR_ADDR_CSZID,
t.T_LNDR_ADDR_DIR_CD = s.T_LNDR_ADDR_DIR_CD,
t.T_LNDR_ADDR_HSE1 = s.T_LNDR_ADDR_HSE1,
t.T_LNDR_ADDR_HSE2 = s.T_LNDR_ADDR_HSE2,
t.T_LNDR_ADDR_MODE = s.T_LNDR_ADDR_MODE,
t.T_LNDR_ADDR_PFX1 = s.T_LNDR_ADDR_PFX1,
t.T_LNDR_ADDR_QDRNT = s.T_LNDR_ADDR_QDRNT,
t.T_LNDR_ADDR_SFX1 = s.T_LNDR_ADDR_SFX1,
t.T_LNDR_ADDR_SFX2 = s.T_LNDR_ADDR_SFX2,
t.T_LNDR_ADDR_ST_CD = s.T_LNDR_ADDR_ST_CD,
t.T_LNDR_ADDR_STRTNM = s.T_LNDR_ADDR_STRTNM,
t.T_LNDR_ADDR_ZIP_CD = s.T_LNDR_ADDR_ZIP_CD,
t.T_LNDR_ADDR1 = s.T_LNDR_ADDR1,
t.T_LNDR_ADDR2 = s.T_LNDR_ADDR2,
t.T_LNDR_ADDR3 = s.T_LNDR_ADDR3,
t.T_LNDR_ADDR4 = s.T_LNDR_ADDR4
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_mtg_grp_info_01_legal_addr,
T_LNDR_ADDR_APTNBR,
T_LNDR_ADDR_CARRT,
T_LNDR_ADDR_CITY,
T_LNDR_ADDR_CNTRY,
T_LNDR_ADDR_CSZID,
T_LNDR_ADDR_DIR_CD,
T_LNDR_ADDR_HSE1,
T_LNDR_ADDR_HSE2,
T_LNDR_ADDR_MODE,
T_LNDR_ADDR_PFX1,
T_LNDR_ADDR_QDRNT,
T_LNDR_ADDR_SFX1,
T_LNDR_ADDR_SFX2,
T_LNDR_ADDR_ST_CD,
T_LNDR_ADDR_STRTNM,
T_LNDR_ADDR_ZIP_CD,
T_LNDR_ADDR1,
T_LNDR_ADDR2,
T_LNDR_ADDR3,
T_LNDR_ADDR4)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_LNDR_ADDR_APTNBR,
s.T_LNDR_ADDR_CARRT,
s.T_LNDR_ADDR_CITY,
s.T_LNDR_ADDR_CNTRY,
s.T_LNDR_ADDR_CSZID,
s.T_LNDR_ADDR_DIR_CD,
s.T_LNDR_ADDR_HSE1,
s.T_LNDR_ADDR_HSE2,
s.T_LNDR_ADDR_MODE,
s.T_LNDR_ADDR_PFX1,
s.T_LNDR_ADDR_QDRNT,
s.T_LNDR_ADDR_SFX1,
s.T_LNDR_ADDR_SFX2,
s.T_LNDR_ADDR_ST_CD,
s.T_LNDR_ADDR_STRTNM,
s.T_LNDR_ADDR_ZIP_CD,
s.T_LNDR_ADDR1,
s.T_LNDR_ADDR2,
s.T_LNDR_ADDR3,
s.T_LNDR_ADDR4);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_tax_exmp_grp_info_01 AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_tax_exmp_grp_info_01 = s.Count1Max,
t.T_UNV_TAX_EXMP_CD = s.T_UNV_TAX_EXMP_CD
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_tax_exmp_grp_info_01,
T_UNV_TAX_EXMP_CD)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_UNV_TAX_EXMP_CD);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sq_ft_grp_info_01 AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sq_ft_grp_info_01 = s.Count1Max,
t.T_SQ_FT_DEPTH_NBR = s.T_SQ_FT_DEPTH_NBR,
t.T_SQ_FT_FRONT_NBR = s.T_SQ_FT_FRONT_NBR,
t.T_SQ_FT_NBR = s.T_SQ_FT_NBR
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sq_ft_grp_info_01,
T_SQ_FT_DEPTH_NBR,
T_SQ_FT_FRONT_NBR,
T_SQ_FT_NBR)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SQ_FT_DEPTH_NBR,
s.T_SQ_FT_FRONT_NBR,
s.T_SQ_FT_NBR);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sit_dpvnote AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sit_dpvnote = s.Count1Max,
t.T_SIT_DPVNOTE = s.T_SIT_DPVNOTE
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sit_dpvnote,
T_SIT_DPVNOTE)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SIT_DPVNOTE);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sel_grp_info_01 AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sel_grp_info_01 = s.Count1Max,
t.T_SEL_FRST_NM = s.T_SEL_FRST_NM,
t.T_SEL_LST_NM = s.T_SEL_LST_NM,
t.T_SEL_MI_NM = s.T_SEL_MI_NM,
t.T_SEL_SFX_ID = s.T_SEL_SFX_ID
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sel_grp_info_01,
T_SEL_FRST_NM,
T_SEL_LST_NM,
T_SEL_MI_NM,
T_SEL_SFX_ID)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SEL_FRST_NM,
s.T_SEL_LST_NM,
s.T_SEL_MI_NM,
s.T_SEL_SFX_ID);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sls_comment_txt AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sls_comment_txt = s.Count1Max,
t.T_SLS_COMMENT_TXT = s.T_SLS_COMMENT_TXT
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sls_comment_txt,
T_SLS_COMMENT_TXT)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SLS_COMMENT_TXT);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_buy_orig_addr AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_buy_orig_addr = s.Count1Max,
t.T_BUY_ORIG_ADDR = s.T_BUY_ORIG_ADDR
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_buy_orig_addr,
T_BUY_ORIG_ADDR)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BUY_ORIG_ADDR);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_buy_addr_dpvnote AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_buy_addr_dpvnote = s.Count1Max,
t.T_BUY_ADDR_DPVNOTE = s.T_BUY_ADDR_DPVNOTE
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_buy_addr_dpvnote,
T_BUY_ADDR_DPVNOTE)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BUY_ADDR_DPVNOTE);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_dist_tax_grp AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_dist_tax_grp = s.Count1Max,
t.T_DIST_CNTY_CD = s.T_DIST_CNTY_CD
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_dist_tax_grp,
T_DIST_CNTY_CD)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_DIST_CNTY_CD);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sls_notes_txt AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sls_notes_txt = s.Count1Max,
t.T_SLS_NOTES_TXT = s.T_SLS_NOTES_TXT
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sls_notes_txt,
T_SLS_NOTES_TXT)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SLS_NOTES_TXT);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sls_legal AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sls_legal = s.Count1Max,
t.T_SLS_LEGAL = s.T_SLS_LEGAL
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sls_legal,
T_SLS_LEGAL)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SLS_LEGAL);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_mtg_grp_info_01_comment AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_mtg_grp_info_01_comment = s.Count1Max,
t.T_MTG_COMMENT_TXT1 = s.T_MTG_COMMENT_TXT1,
t.T_MTG_COMMENT_TXT2 = s.T_MTG_COMMENT_TXT2,
t.T_MTG_COMMENT_TXT3 = s.T_MTG_COMMENT_TXT3
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_mtg_grp_info_01_comment,
T_MTG_COMMENT_TXT1,
T_MTG_COMMENT_TXT2,
T_MTG_COMMENT_TXT3)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_MTG_COMMENT_TXT1,
s.T_MTG_COMMENT_TXT2,
s.T_MTG_COMMENT_TXT3);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sel_cd_tabl_grp AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sel_cd_tabl_grp = s.Count1Max,
t.T_SEL_CD_TABL_IND = s.T_SEL_CD_TABL_IND
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sel_cd_tabl_grp,
T_SEL_CD_TABL_IND)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SEL_CD_TABL_IND);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_buy_cd_tabl_grp AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_buy_cd_tabl_grp = s.Count1Max,
t.T_BUY_CD_TABL_IND = s.T_BUY_CD_TABL_IND
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_buy_cd_tabl_grp,
T_BUY_CD_TABL_IND)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BUY_CD_TABL_IND);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_buy_co_nm AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_buy_co_nm = s.Count1Max,
t.T_BUY_CO_NM = s.T_BUY_CO_NM
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_buy_co_nm,
T_BUY_CO_NM)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BUY_CO_NM);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_mtg_grp_info_02 AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_mtg_grp_info_02 = s.Count1Max,
t.T_MOD_DUE_DT = s.T_MOD_DUE_DT,
t.T_MOD_INT_RT_PCT = s.T_MOD_INT_RT_PCT,
t.T_MOD_MTG_AMT = s.T_MOD_MTG_AMT,
t.T_MTG_CHANGE_AMT = s.T_MTG_CHANGE_AMT,
t.T_MTG_CONCR_JR_IND = s.T_MTG_CONCR_JR_IND,
t.T_MTG_PAYOFF_DT = s.T_MTG_PAYOFF_DT,
t.T_MTG_PAYOFF_TYP = s.T_MTG_PAYOFF_TYP,
t.T_MTG_RLSE_DT = s.T_MTG_RLSE_DT,
t.T_MTG_SLNT_2ND_IND = s.T_MTG_SLNT_2ND_IND
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_mtg_grp_info_02,
T_MOD_DUE_DT,
T_MOD_INT_RT_PCT,
T_MOD_MTG_AMT,
T_MTG_CHANGE_AMT,
T_MTG_CONCR_JR_IND,
T_MTG_PAYOFF_DT,
T_MTG_PAYOFF_TYP,
T_MTG_RLSE_DT,
T_MTG_SLNT_2ND_IND)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_MOD_DUE_DT,
s.T_MOD_INT_RT_PCT,
s.T_MOD_MTG_AMT,
s.T_MTG_CHANGE_AMT,
s.T_MTG_CONCR_JR_IND,
s.T_MTG_PAYOFF_DT,
s.T_MTG_PAYOFF_TYP,
s.T_MTG_RLSE_DT,
s.T_MTG_SLNT_2ND_IND);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sel_addr AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sel_addr = s.Count1Max,
t.T_SEL_ADDR = s.T_SEL_ADDR
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sel_addr,
T_SEL_ADDR)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SEL_ADDR);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sel_orig_addr AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sel_orig_addr = s.Count1Max,
t.T_SEL_ORIG_ADDR = s.T_SEL_ORIG_ADDR
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sel_orig_addr,
T_SEL_ORIG_ADDR)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SEL_ORIG_ADDR);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_mtg_grp_info_01_int_rt AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_mtg_grp_info_01_int_rt = s.Count1Max,
t.T_INT_RT_CHG_DT = s.T_INT_RT_CHG_DT,
t.T_INT_RT_CHG_FREQ = s.T_INT_RT_CHG_FREQ,
t.T_INT_RT_CHG_INTVL = s.T_INT_RT_CHG_INTVL,
t.T_INT_RT_CHG_PCT = s.T_INT_RT_CHG_PCT,
t.T_INT_RT_CHG_PCTLM = s.T_INT_RT_CHG_PCTLM,
t.T_INT_RT_INDEX_TYP = s.T_INT_RT_INDEX_TYP,
t.T_INT_RT_PCT_MAX = s.T_INT_RT_PCT_MAX
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_mtg_grp_info_01_int_rt,
T_INT_RT_CHG_DT,
T_INT_RT_CHG_FREQ,
T_INT_RT_CHG_INTVL,
T_INT_RT_CHG_PCT,
T_INT_RT_CHG_PCTLM,
T_INT_RT_INDEX_TYP,
T_INT_RT_PCT_MAX)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_INT_RT_CHG_DT,
s.T_INT_RT_CHG_FREQ,
s.T_INT_RT_CHG_INTVL,
s.T_INT_RT_CHG_PCT,
s.T_INT_RT_CHG_PCTLM,
s.T_INT_RT_INDEX_TYP,
s.T_INT_RT_PCT_MAX);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_sel_co_nm AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_sel_co_nm = s.Count1Max,
t.T_SEL_CO_NM = s.T_SEL_CO_NM
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_sel_co_nm,
T_SEL_CO_NM)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_SEL_CO_NM);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_prin_addr AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_prin_addr = s.Count1Max,
t.T_PRIN_ADDR = s.T_PRIN_ADDR
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_prin_addr,
T_PRIN_ADDR)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_PRIN_ADDR);
;WITH s as (SELECT d.DataSupplierId, s.* FROM DiabloStg.DiabloLoad.' + @DataFolder + '_trans_t_bld_comment_txt AS s (NOLOCK) INNER JOIN ' + @DBNAME + '.tCommon.DataSupplier d (NOLOCK) ON d.CntyCd = s.CNTY_CD)
MERGE DiabloStg.DiabloLoad.' + @DataFolder + '_trans_pivoted WITH (TABLOCK) AS t
USING s
ON t.DataSupplierId = s.DataSupplierId
AND t.T_BATCH_DT = s.T_BATCH_DT
AND t.T_BATCH_SEQ = s.T_BATCH_SEQ
AND t.Count1 = s.Count1
WHEN MATCHED THEN UPDATE SET
t.Count1Max_bld_comment_txt = s.Count1Max,
t.T_BLD_COMMENT_TXT = s.T_BLD_COMMENT_TXT
WHEN NOT MATCHED THEN
INSERT (DataSupplierId,
T_BATCH_DT,
T_BATCH_SEQ,
Count1,
Count1Max_bld_comment_txt,
T_BLD_COMMENT_TXT)
VALUES
(s.DataSupplierId,
s.T_BATCH_DT,
s.T_BATCH_SEQ,
s.Count1,
s.Count1Max,
s.T_BLD_COMMENT_TXT);
'
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
EXEC(@SQL)
GO
EXEC Template.LoadTransPivotedTableCreate_4
@DataFolder = 'db015_14',
@PrintSQL = 1,
@ExecSQL = 0
GO