I am trying to update an empty table I just created ([LA_MCO_EQR_MED_201612_20171116] t1) with records from a view ([vClaim] t2). I know the update statement isn't working because of the join. Since t1 is a new (empty) table, I won't return any results because of the join. What is the best way to accomplish this task? I know I am missing something simple.
Update [LA_MCO_EQR_MED_201612_20171116] --empty table
Set [Claim_Sys_ICN] = t2.ClaimID,
Claim_Line_Number = ClaimLine,
MCO_Claim_Status = Case When ClaimStatus = 'PAID' Then 'P'
When ClaimStatus = 'ADJUCATED' Then 'A'
When ClaimStatus = 'DENIED' Then 'D'
When ClaimStatus = 'REVERSED' Then 'R'
When ClaimStatus = 'VOID' Then 'V' End,
Patient_Account_Number = CarrierMemID,
MCO_Paid_Date = PaidDate,
Paid_Provider_NPI = PayToNPI,
Procedure_Code = ProcCode,
Procedure_Code_Modifier_1 = Modifier,
Procedure_Code_Modifier_2 = Modifier2,
Procedure_Code_Modifier_3 = Modifier3,
Procedure_Code_Modifier_4 = Modifier4,
Service_Provider_NPI = RenderingNPI,
HDR_Clm_Paid_Amount = AmountPaid
FROM [LA_MCO_EQR_MED_201612_20171116] t1 --empty table
LEFT JOIN [vClaim] t2 on --need records from this view
t1.[Claim_Sys_ICN] = t2.ClaimID
Where PaidDate Between '12/1/2016' and '12/31/2016'
Same idea using INSERT INTO...
INSERT INTO [LA_MCO_EQR_MED_201612_20171116]
([Claim_Sys_ICN], Claim_Line_Number,
MCO_Claim_Status, Patient_Account_Number, MCO_Paid_Date, Paid_Provider_NPI,
Procedure_Code, Procedure_Code_Modifier_1, Procedure_Code_Modifier_2,
Procedure_Code_Modifier_3, Procedure_Code_Modifier_4,
Service_Provider_NPI, HDR_Clm_Paid_Amount)
Select ClaimID, ClaimLine, Case When ClaimStatus = 'PAID' Then 'P'
When ClaimStatus = 'ADJUCATED' Then 'A'
When ClaimStatus = 'DENIED' Then 'D'
When ClaimStatus = 'REVERSED' Then 'R'
When ClaimStatus = 'VOID' Then 'V' End,
CarrierMemID, PaidDate, PayToNPI, ProcCode, Modifier, Modifier2, Modifier3,
Modifier4, RenderingNPI, AmountPaid
FROM [vClaim]
Where PaidDate Between '12/1/2016' and '12/31/2016'
Do an INSERT instead of an UPDATE.