sql - Inserting data scattered from multiple tables into a table

Question

I'm at beginners level in SQL and I hope that get guidance.

Table 1, 2 and 3 are Tables without a UniqueID.

TABLE1

TABLE2

TABLE3

Then I inserted all records of the 3 tables into Table4 with a uniqueID and source table column.

TABLE4

My goal is to insert data from Table 4 INTO Table5 but instead of creating an update statement on each NULL records per column. Is there a better way to insert it in one query? I tried using row partition and case function but it doesn't work.

Desired Table5 output.

Edit: For EZRA FITCH STATE = OREGON, it is entered according to the table rank. Rank 1=Table1 Rank 2=Table2 Rank 3=Table3

question from:https://stackoverflow.com/questions/65877481/inserting-data-scattered-from-multiple-tables-into-a-table

Answer 1

use this code:

 INSERT INTO Table4 (FirstName, LastName, State, Position, EmploymentStatus, Source)
        SELECT FirstName, LastName, State, Position, EmploymentStatus, 'Table1' FROM Table1;
        
    INSERT INTO Table4 (FirstName, LastName, State, Position, EmploymentStatus, Source)
        SELECT FirstName, LastName, State, Position, EmploymentStatus, 'Table2' FROM Table2;
        
    INSERT INTO Table4 (FirstName, LastName, State, Position, EmploymentStatus, Source)
        SELECT FirstName, LastName, State, Position, EmploymentStatus, 'Table3' FROM Table3;


 INSERT INTO Table5(FirstName, LastName, State, Position, EmploymentStatus)
  SELECT
    MAX(FirstName),MAX(LastName),MAX(State),MAX(Position),MAX(EmploymentStatus)
FROM table4
group by FirstName , LastName

this code work

table5 output: