当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - Detecting and Updating a small number of changes in a table

问题描述:

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:

  • Write the content of the entire spreadsheet into a temporary table
  • Execute a merge of the employee table and the temporary table using SSN match as your merge search condition
  • Use WHEN NOT MATCHED BY SOURCE for changing the EndDate
  • Use WHEN NOT MATCHED BY TARGET for adding new employees
  • Use WHEN MATCHED for 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

'Existing employee

--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
分享给朋友:
您可能感兴趣的文章:
随机阅读: