I have three tables.
Table 1
| ClaimID | Patient | Proc_Code| Proc_Cost|
| 1 | Tom | DTR | 20 |
| 1 | Tom | OPR | 30 |
| 1 | Tom | TRR | 20 |
| 2 | Bruce | YUR | 70 |
Table 2
| ClaimID |PatientDOB| S_Name | Date |
| 1 | 12-2-1989| ABC | 11-2-2022|
| 2 | 10-3-1987| ABC | 11-2-2022|
| 3 | 12-2-1989| ABC | 11-2-2022|
| 4 | 12-2-1989| ABC | 11-2-2022|
Table 3
| ClaimID |PatientDOB| PName | Date |Proc_Code | Proc_Cost|
| 1 | 12-2-1989| Tom | 11-2-2022|Null | Null |
| 2 | 10-3-1987| Hary | 11-2-2022|Null | Null |
| 3 | 12-2-1989| Mary | 11-2-2022|Null | Null |
| 4 | 12-2-1989| Steve | 11-2-2022|Null | Null |
Table 1 & 2 are updated automatically when we ingest EDI file from EDI converter. Table 3, I update with left join from Table 2 and insert into statement. Table 1 has different Proc code and Proc cost for each claimID. I want to update these to table 3 which would add a new row and update the Proc Code and Proc Cost. It should look something like this:
Table 3
| ClaimID |PatientDOB| PName | Date |Proc_Code | Proc_Cost|
| 1 | 12-2-1989| Tom | 11-2-2022| DTR | 20 |
| 1 | 12-2-1989| Tom | 11-2-2022| OPR | 30 |
| 1 | 12-2-1989| Tom | 11-2-2022| TRR | 20 |
| 2 | 10-3-1987| Hary | 11-2-2022| YUR | 70 |
| 3 | 12-2-1989| Mary | 11-2-2022|#value | #value |
| 4 | 12-2-1989| Steve | 11-2-2022|#value | #value |
#value = some value that will be in table 1.
ClaimID #2 will also have multiple Proc code and Proc cost. So will other Claim IDs. I am unsure as to how do I do this? I tried joining Table 3 with Table 1, the problem that arise was that all the information from Table 1 was pasted on the bottom rather that how I want it to be.
I tried update as well:
UPDATE table3
SET table3.Proc_Code = table1.Proc_Code
FROM table3
JOIN table1 ON table3.ClaimID = table1.ClaimID
WHERE table3.ClaimID = table1.ClaimID;
This works but it only updates the top one claimeID and ignores other. I am thinking there should be a loop? that will update and add new row for the new value? I am not sure how to do it?