DECLARE --Put initial Selected data
@InitialRawData Table(
RowNum int IDENTITY(1,1) NOT NULL ,
EmployeeCode nvarchar(200) ,
MotherName nVarchar(300) NULL
--MotherName nVarchar(300) NULL ,
--SpouseName nVarchar (300) NULL ,
--Height nVarchar(300) ,
-- Blood nvarchar(20)
)
INSERT INTO @InitialRawData
SELECT
EmployeeCode, [Mothers Name]
FROM UpdateAdvice as ls
SELECT * FROM @InitialRawData
DECLARE @LeaveSELLRow INT
,@CountLS INT
SET @LeaveSELLRow = 1
SET @CountLS = (SELECT COUNT(*) FROM @InitialRawData)
WHILE @LeaveSELLRow <= @CountLS - 1
BEGIN
Declare @EmpId bigint, @CurrEmployeeCode nvarchar(200), @CurrMotherName nvarchar(20), @Gender nvarchar(10)
SELECT @CurrEmployeeCode = EmployeeCode, @CurrMotherName = MotherName FROM @InitialRawData Where RowNum = @LeaveSELLRow
SELECT @Gender = Gender, @EmpId = EmployeeId FROM Employee WHERE EmployeeCode = @CurrEmployeeCode
--F
--M
IF EXISTS
(
SELECT *
FROM EmployeeFamilyInfo
WHERE EmployeeId = @EmpId AND Relation = 'M'
)
BEGIN -- Update
Update EmployeeFamilyInfo SET Name = @CurrMotherName WHERE EmployeeId = @EmpId AND Relation = 'M'
END
ELSE -- INSERT
BEGIN
Insert INTO EmployeeFamilyInfo
(
EmployeeId, Name, Relation, Gender, DateOfBirth, EducationalQualification, Occupation, IsActive, InActiveDate, IsApproved, IsRejected, ApprovedOrRejectedBy, ApprovalOrRejectDate, CreateUser, CreateDate, UpdateUser, UpdateDate
)
Values
(
@EmpId, @CurrMotherName, 'M', 'F', NULL, NULL, NULL, 1, NULL, 1, 0, NULL, NULL, 3, '2018-07-11 12:53:00', NULL, NULL
)
END
SET @LeaveSELLRow += 1
END
