Last post Nov 19, 2018 02:52 PM by limno
Nov 19, 2018 07:31 AM|JagjitSingh|LINK
I have below code . In case of Insert is o.k . I want if Employee Id exists is Employee Transactions then instead of Insert Record should be Updated
INSERT INTO dbo.Employee_Transactions(EmployeeID,FullName,BaseAmount,
FROM dbo.Employee A Inner Join dbo.Employee_Details B on A.[EmployeeID] = B.[EmployeeID]
and B.EffectiveDate <= (Select Max(EffectiveDate) from dbo.Employee_Details where EmployeeID = A.EmployeeID)
where Not Exists (Select [EmployeeID] FROM dbo.Employee_Transactions as C where [EmployeeID] = A.[EmployeeID]
Nov 19, 2018 02:20 PM|PatriceSc|LINK
You could just run an update statement (with an inner join) prior to this insert so that you update matching rows first and then insert missing rows. If using a recent SQL Server version you could also consider using
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 (though I prefer to use that when doing a full synchronization between tables).
You are not showing the exact INSERT so I'm not sure to fully get which kind of join you want . EffectiveDate would match StartDate ??? This is basically a kind of history table ?
Nov 19, 2018 02:52 PM|limno|LINK
A merge statement should handle both insert and update. Study the syntax from MSDN:
You cannot learn TSQL skills only by asking everything you don't know on a forum. Take a book on this topic, it will speed up your learning process.