Hi Jaseem0712,
Based on the description you posed in the stackoverflow, it seems your are looking for a
MERGE statement.
To achieve your requirement, please reference below sample.
CREATE TABLE Trans_Details
(
empId INT,
transDateTime DATE,
Created_Date DATE,
LeaveStatus VARCHAR(99)
);
CREATE TABLE Trans_Leave
(
EMPLOYEE_NUMBER INT,
NAME VARCHAR(99),
DATE_START DATE,
DATE_END DATE
);
INSERT INTO Trans_Leave
VALUES
(1,'annual leave','20150601','20150610');
;WITH Cte AS
(
SELECT CAST('20150601' AS DATE) DT
UNION ALL
SELECT DATEADD(DAY,1,DT) FROM Cte
WHERE DT<'20150610'
)
,Cte2 AS
(
SELECT * FROM Cte c JOIN Trans_Leave t
ON c.DT BETWEEN t.DATE_START AND t.DATE_END
)
MERGE Trans_Details Tar
USING Cte2 Src
ON Tar.empId=Src.Employee_number
AND Tar.transDateTime=Src.DT
WHEN MATCHED THEN
UPDATE SET Tar.LeaveStatus= Src.Name
WHEN NOT MATCHED THEN
INSERT(empId,transDateTime,Created_Date,LeaveStatus)
VALUES (Src.Employee_number,Src.DT,GETDATE(),Src.Name);
SELECT * FROM Trans_Details
If you have any question, feel free to let me know.