[SOLVED] Update Empty Table with Records from a View

Update Empty Table with Records from a View

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'

Solution

Do an INSERT instead of an UPDATE.