Creating the same unique ID in multiple tables I have two tables of data; one table is the Employee Master table containing a list of unique employee names, and the other table contains transactional data with multiple records for each employee. 

There is no unique number that ties the two tables together. I would like to assign the same unique ID to the employee names within each table. 

For the Employee Master table, I was able to do this with this code:

select employee_name, row_number() Over(order by employee_name asc) as employee_id
from HREmployeeMaster
group by employee_name


However, no matter what I try, for the transaction table, I cannot assign one ID to the distinct employee names. Instead, I keep getting an ID for each record in the table.

Any advice would be appreciated.
Add EmployeeId  to columns to both tables.  Populate the master EmployeeId  with the query you have, and join the transaction details table to the employee master on EmployeeName  and lookup the EmployeeId .

eg

with q as
(
   select e.EmployeeId master_EmployeeId, t.EmployeeId
   from HREmployeeMaster e
   join EmployeeTransactions t
     on e.EmployeeName = t.EmployeeName
)
update q set EmployeeId  = master_EmployeeId;