I have a table of employees of a company.
Everyday, I am getting a spreadsheet of the latest list of employees of the company. If any person (based on SSN) in the table is not in the spreadsheet, that employee has left and an
EndDate column in the table is updated. If a person is in the spreadsheet but not in the table, it is a new employee and has to be inserted. Otherwise, it is an existing employee, and zero or more columns (eg telphone number, supervisor, etc) could have changed and that row in the table has to be udpated.
Given that the company size is just under 1,000 employees, and that each day the changes are about 50 employees, what is the recommended way to do this data refresh?
I understand that there is no one best solution, but would like to see what creative implementations there are out there. Thanks.
SQL Server provides a special merge command for situations like this.
You can use this simple approach:
WHEN NOT MATCHED BY SOURCEfor changing the
WHEN NOT MATCHED BY TARGETfor adding new employees
WHEN MATCHEDfor all remaining updates.
You can create
SSIS package for this and automate it when ever you get a new spreadsheet.The design could be as simple as the one below
As per your requirement you describe i come up with one idea/solution which i will use..
Step 1: Create Employee.CSV File With req. employee filed wich need to Add/Updated in Database.Ex(Name,Add1,Add2,City,State,ZIp,SSN.....)
Step 2:Create One page in Application wich upload the csv file..I will read CSV file and create generic List(Employee) object.
Step 3 : Call Stored procedure which insert/Update Data from employee object
1) Impalement Transaction in code. 2) loop throw each employee in object. 3) Rollback if any Error Occurred. 4) Implement Stored procedure in database for your case like..
if (Ltrim(Rtrim(isnull(@ssN,''))=='')) 'Your logic return end if
if (ssn != '') 'Edit case return end if
--Sample of SP
CREATE PROCEDURE sp_Uploademployee ( @cCode VARCHAR(200), @cName VARCHAR(500), .....ETC ) AS SET NOCOUNT ON DECLARE @ID INT IF ISNULL(@cName,'') <> '' And ISNULL(@ccode,'')<> '' BEGIN IF NOT EXISTS (SELECT iID FROM employee WHERE [email protected] AND [email protected] ) BEGIN INSERT INTO employee () VALUES () END ELSE BEGIN END END ELSE BEGIN RAISERROR('your Error', 16, -1) RETURN END