SP Help
-
Hi I am using the following SP to append my employees table with new rows if any, but how can I update employees table if there are any changes in some existing columns, with in the same SP.
CREATE PROCEDURE UpdEmp
As
SET NOCOUNT ON
INSERT INTO employees
(
EmpId,
LastName,
FirstName,
MiddleInitial,
JobTitleID,
JobTitle
 
SELECT
EmpId,
LastName,
FirstName,
MiddleInitial,
JobTitleID,
JobTitle
FROM EmpSource
WHERE EmpId NOT IN
(
SELECT
EmpId
FROM Employees
)
GO
-
If Exists (Select empid From dbo.employees where empid = @empid)
Begin
Update
--test for error and take action ...
End
Else
Begin
Insert
--test for error and take action ...
End
-
Hi thanks for the quick reply, as i am new to T SQL..
Here this SP, I want to shedule it to run by itself, I dont want to pass any parameters manually. It has to check the EmpSource table if any changes occured for the existing columns then update employees table.
-
CREATE PROCEDURE UpdEmp
As
SET NOCOUNT ON
UPDATE e
SET e.LastName = s.LastName,
e.FirstName = s.FirstName,
e.MiddleInitial = s.MiddleInitial,
e.JobTitleID = s.JobTitleID,
e.JobTitle = s.JobTitle
FROM [Employees] e
INNER JOIN [EmpSource] s
ON s.EmpId = e.EmpId
AND (s.LastName <> e.LastName
OR s.FirstName <> e.FirstName
OR s.MiddleInitial <> e.MiddleInitial
OR s.JobTitleID <> e.JobTitleID
OR s.JobTitle <> e.JobTitle)
INSERT INTO [Employees]
(EmpId,LastName,FirstName,MiddleInitial,JobTitleID,JobTitle)
SELECT s.EmpId,s.LastName,s.FirstName,s.MiddleInitial,s.JobTitleID,s.JobTitle
FROM [EmpSource] s
LEFT OUTER JOIN [Employees] e
ON e.EmpId = s.EmpId
AND e.LastName = s.LastName
AND e.FirstName = s.FirstName
AND e.MiddleInitial = s.MiddleInitial
AND e.JobTitleID = s.JobTitleID
AND e.JobTitle = s.JobTitle
WHERE e.EmpId IS NULL
GO
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply