Last post Mar 03, 2016 05:07 AM by navneetmitawa
Mar 03, 2016 04:39 AM|Chithra_Iyer|LINK
I have the below 3 tables in DB.
EmployeeMasterData is the master table and it has foreign keys DesignationId & DepartmentId from EmployeeDesignation and EmployeeDepartment.
The problem is i need to update this EmployeeMasterData from a temporary table and this table has only Designation & Department.So need to fetch corresponding
DesignationId & DepartmentId and update EmployeeMasterData.
EmpId | FName |LName | DesignationId
DesignationId | Designation
DepartmentId | Department
Iam doing a bulk copy from excel to a temporary table and from this table i need to update EmployeeMasterData.So in this temporary table I have the below columns
EmpId Department Designation Dob ... etc and all other data present in EmployeeMasterData expect the foreign keys DepartmentId & DesignationId.
From this temporary table I have to update the EmployeeMasterData.
How can i get the DepartmentId and DesignationId from the EmployeeDepartment and EmployeeDesignation master table based on
each EmpID and update the DepartmentId & DesignationId on EmployeeMasterData table for all employees.
Please help me out with a sample sql query
Mar 03, 2016 05:07 AM|navneetmitawa|LINK
Try this if your user define table is @tbl which having columns likes EmpId Department Designation Dob.
SET DesignationId = Isnull(Ed.DesignationId,EmployeeMasterData.DesignationId)
From @tbl as temptable
Left Join EmployeeDesignation as Ed
on Lower(Rtrim(Ltrim(Ed.Designation))) = Lower(Rtrim(Ltrim(temptable.Designation)))
Where temptable.empid = EmployeeMasterData.empid
and you not define DOB and department reference in EmployeeMasterData
if i'm right then EmployeeMasterData have reference of both.