Auto populate SQL table fields from another tables fields-CodePudding

I am currently building a SQL database to monitor access to a server room. I have table1 with the employees details. The primary key is the employeeID field. I have table2 which is the transaction produced from the door reader. When a new row is inserted into table2 the RFID reader will produce the time/date and employeeID. I would like table2 to auto populate the employee name field by matching the employeeID’s in table1 and table2. Should I be using a SQL view to complete this task?

Table 1

EmployeeID, FirstName, LastName

Table2

Time/date, EmployeeID, FirstName, LastName

CodePudding user response：

I would do something like this,

Table1

EmployeeID, FirstName, LastName

Table2

Id, Time/date, EmployeeID

When you want to view the result,

Select Table2.Time/date, Table1.EmployeeId, Table1.FirstName, Table1.LastName From Table2 Left Join Table1 On Table2.EmployeeId = Table1.EmployeeId