Search code examples
sql-serverrfid

Auto populate SQL table fields from another tables fields

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

Solution

  • 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