XPCMD_SHELL Move & Load Data from Directory

USE [myETL]

GO

/****** Object:  StoredProcedure [dbo].[LoadMyRxData]    Script Date: 10/22/2021 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoadMyRxData]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[LoadMyRxData]

GO

/****** Object:  StoredProcedure [dbo].[LoadMyRxData]    Script Date: 10/22/2021 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[LoadMyRxData]

AS

exec xp_cmdshell 'move \\DatabaseServer07\FTP\DataReporting\*PatDetails* \\DatabaseServer01\F\docs\DbName\MyRxData\'--,no_output

--Load Raw files

declare @dir table(itm varchar(500),ID int identity)

declare @pth varchar(1000) = '\\DatabaseServer01\f\docs\DbName\MyRxData\'

declare @cmd varchar(max)

declare @fl varchar(1000)

delete from @dir

insert @dir(itm)

exec xp_cmdshell 'dir /b \\DatabaseServer01\f\docs\DbName\MyRxData\*PatDetails*dat'

while (select COUNT(*) from @dir where isnull(itm,'File Not Found')<>'File Not Found')>0

begin

select top 1 @fl = itm from @dir where itm is not null order by itm

if(OBJECT_ID('tempdb..##FL_MyRxData') is not null) drop table ##FL_MyRxData

create table ##FL_MyRxData(dta varchar(max))

set @cmd='bulk insert ##FL_MyRxData from '''+@pth+@fl+''' with(RowTerminator=''0x0a'')'

exec(@cmd)

if(OBJECT_ID('tempdb..##PharmRx') is not null) drop table ##PharmRx

select top 1 @cmd= dta from ##FL_MyRxData where CHARINDEX('CLIENT_ID',dta)<>0

set @cmd=REPLACE(@cmd,' ','')

set @cmd=REPLACE(@cmd,'&','')

set @cmd=REPLACE(@cmd,'/','')

set @cmd=REPLACE(@cmd,',','')

set @cmd=REPLACE(@cmd,'|',' varchar(1000),')

if(RIGHT(@cmd,1)<>'|') set @cmd = @cmd+' varchar(1000)'

set @cmd = 'create table ##PharmRx('+@cmd+')'

exec(@cmd)

set @cmd='bulk insert ##PharmRx from '''+@pth+@fl+''' with(RowTerminator=''0x0a'',fieldterminator=''|'')'

exec(@cmd)

delete from ##PharmRx where CLIENT_ID='CLIENT_ID'

alter table ##PharmRx add FLENAME varchar(500)

update ##PharmRx set FLENAME=@fl

--prevent loading duplicate records

delete from ##PharmRx where

CLIENT_ID+PHARMACY+RX_NBR+FILL_NBR+CLAIM_STATUS+ADJUD_DT

in (select CLIENT_ID+PHARMACY+RX_NBR+FILL_NBR+CLAIM_STATUS+ADJUD_DT from DbName.dbo.PharmacyRx) 

--Load the data

declare @ts datetime=getdate()

insert DbName.dbo.PharmacyRx

(CLIENT_ID,

HRSA_ID,

ENTITY,

CLIENT,

CLINIC,

DEPARTMENT,

LAST_NAME,

FIRST_NAME,

DOB,

ID,

CLAIM_TYPE,

RX_NBR,

FILL_NBR,

ADJUD_DT,

PHARMACY,

PHARMACY_LOCATION,

PRESCRIBER_NAME,

PHARMACY_NPI,

GROUP_ID,

GROUP_NAME,

PRESCRIBER_NPI,

E_RX_FLAG,

SOURCE_TYPE,

THERAPEUTIC_CLASS_DESC,

CLASS_IND,

DRG_TYP,

NDC,

DRG_LBL_NM,

QTY_DISP,

DOS,

UNIT_COST_340B,

COST_340B,

AWP_PRICE,

AWP_COST,

RX_WRITTEN_DT,

FILL_SOLD_DTTM,

CLAIM_STATUS,

PLN_AR_AMT,

COPAY_AMT,

SALES_TAX,

ADMN_FEE,

DISP_FEE,

THRDPTY_FEE,

CLIENT_FEE,

TOT_DUE_WAG,

BillingMonth340B,

FLENAME,

LoadDT)

select

CLIENT_ID,

HRSA_ID,

ENTITY,

CLIENT,

CLINIC,

DEPARTMENT,

LAST_NAME,

FIRST_NAME,

DOB,

ID,

CLAIM_TYPE,

RX_NBR,

FILL_NBR,

ADJUD_DT,

PHARMACY,

PHARMACY_LOCATION,

PRESCRIBER_NAME,

PHARMACY_NPI,

GROUP_ID,

GROUP_NAME,

PRESCRIBER_NPI,

E_RX_FLAG,

SOURCE_TYPE,

THERAPEUTIC_CLASS_DESC,

CLASS_IND,

DRG_TYP,

NDC,

DRG_LBL_NM,

QTY_DISP,

DOS,

UNIT_COST_340B,

COST_340B,

AWP_PRICE,

AWP_COST,

RX_WRITTEN_DT,

FILL_SOLD_DTTM,

CLAIM_STATUS,

PLN_AR_AMT,

COPAY_AMT,

SALES_TAX,

ADMN_FEE,

DISP_FEE,

THRDPTY_FEE,

CLIENT_FEE,

TOT_DUE_WAG,

Billing_Month_340B,

FLENAME,

@ts LoadDT

from

##PharmRx cc

truncate table ##PharmRx 

--move the file to the archive

set @cmd='exec xp_cmdshell ''move "'+@pth+@fl+'"'

set @cmd=@cmd+' '+@pth+'archive\'''

exec(@cmd)

--find next file

delete from @dir

insert @dir(itm)

exec xp_cmdshell 'dir /b \\DatabaseServer01\f\docs\DbName\PharmRX\*PatDetails*dat'

end

select FleName,LoadDT,count(*) from DbName.dbo.PharmacyRx group by FleName,LoadDT order by 2 desc

GO