Deletion of Muliple record through Openxml on composite key

//Reading Reocord from open XML in Temp Table

SELECT * into #TempData160 FROM OPENXML (@idoc, '/ROOT/tblHHA160',1) WITH (
ROW_NO INT,
HHA_REC_ID VARCHAR(2),REC_TYPE VARCHAR(2),LOCK_DATE varchar(20),CORRECTION_NUM varchar(2),ACY_DOC_CD VARCHAR(8),
VERSION_CD1 VARCHAR (12),VERSION_CD2 VARCHAR(5),SFTW_ID VARCHAR(9),SFT_VER VARCHAR(5),HHA_HHA_AGENCY_ID VARCHAR(16),
PAT_ID VARCHAR(14),ST_CODE VARCHAR (2),ST_ERR_CNT VARCHAR(4),ST_COR CHAR (1),ST_PMT_COR CHAR (1),ST_KEY_COR CHAR (1),
ST_DELETE CHAR (1) ,MC_COR CHAR (1),MC_PMT_COR CHAR (1),MC_KEY_COR CHAR (1),MASK_VERSION_CD VARCHAR (20),CNT_FILLER VARCHAR (7),
M0010_MEDICARE_ID VARCHAR (6),M0012_MEDICAID_ID VARCHAR (15),M0014_BRANCH_STATE VARCHAR (2),M0016_BRANCH_ID VARCHAR (10),
M0020_PAT_ID VARCHAR (20),M0030_START_CARE_DT varchar(20)

)
//Check exists function


if(exists(SELECT tblHHA160.M0010_MEDICARE_ID,tblHHA160.M0020_PAT_ID,tblHHA160.M0030_START_CARE_DT,tblHHA160.M0100_ASSMT_REASON
FROM tblHHA160 INNER JOIN
#TempData160 AS tblHHA160_1 ON tblHHA160.M0010_MEDICARE_ID = tblHHA160_1.M0010_MEDICARE_ID AND
tblHHA160.M0020_PAT_ID = tblHHA160_1.M0020_PAT_ID AND tblHHA160.M0030_START_CARE_DT = tblHHA160_1.M0030_START_CARE_DT AND
tblHHA160.M0100_ASSMT_REASON = tblHHA160_1.M0100_ASSMT_REASON AND tblHHA160.M0016_BRANCH_ID = tblHHA160_1.M0016_BRANCH_ID))



//Delete record on composite key in table from temp Table
delete tblHHA160 from tblHHA160 INNER JOIN
#TempData160 AS tblHHA160_1 ON tblHHA160.M0010_MEDICARE_ID = tblHHA160_1.M0010_MEDICARE_ID AND
tblHHA160.M0020_PAT_ID = tblHHA160_1.M0020_PAT_ID AND tblHHA160.M0030_START_CARE_DT = tblHHA160_1.M0030_START_CARE_DT AND
tblHHA160.M0100_ASSMT_REASON = tblHHA160_1.M0100_ASSMT_REASON AND tblHHA160.M0016_BRANCH_ID = tblHHA160_1.M0016_BRANCH_ID

Comments

Popular posts from this blog

OutSystems – A Low-code Development Platform

What is difference between Azure Cognitive Search and Elastic Search

failed to access iis metabase asp.net