Update data in SQL Table row by row

Question

I'm using SQL Server 2008 R2. I have a View [EmployeeMaster] from a Linked Server and a table [EmployeeDetails] in database. i have a service which run at 12 AM everyday calling a stored procedure which sync the table with view. ie, stored procedure check and update each row in [EmployeeDetails] with the matching row from view. My stored procedure goes like this.

CREATE PROCEDURE usp_OracleSyncUpdate
AS

CREATE TABLE #ActiveEmployees (
 RowID int IDENTITY(1, 1), 
 [EmployeeId] nvarchar(50),
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @EmployeeId nvarchar(50)
        ,@EmployeeName nvarchar(50)
        ,@EmployeeLastName nvarchar(50)
        ,@EmployeeCategory nvarchar(50)
        ,@ContactNo nvarchar(50)
        ,@Email nvarchar(50)
        ,@Gender nvarchar(50)
        ,@JoiningDate DATETIME

-- into the temporary table
INSERT INTO #ActiveEmployees ([EmployeeId])
SELECT [EMPLOYEE_NUMBER]
FROM [dbo].[EmployeeMaster]
WHERE [EMPLOYEE_NUMBER] IN (
        SELECT EmployeeId
        FROM [dbo].[EmployeeDetails]
        )

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @EmployeeId = EmployeeId
 FROM #ActiveEmployees
 WHERE RowID = @RowCount

 SELECT @EmployeeName = EmployeeName FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@EmployeeName)) <> (SELECT LTRIM(RTRIM([FIRST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET EmployeeName = (SELECT LTRIM(RTRIM([FIRST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @EmployeeLastName = EmployeeLastName FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@EmployeeLastName)) <> (SELECT LTRIM(RTRIM([LAST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET EmployeeLastName = (SELECT LTRIM(RTRIM([LAST_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @EmployeeCategory = [Category] FROM [dbo].[EmployeeCategory] WHERE [EmployeeCategoryId] = (SELECT [EmployeeCategoryId] FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId)
 IF(LTRIM(RTRIM(@EmployeeCategory)) <> (SELECT LTRIM(RTRIM([JOB_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET [EmployeeCategoryId] = (SELECT [EmployeeCategoryId] FROM [dbo].[EmployeeCategory] WHERE [Category] = (SELECT LTRIM(RTRIM([JOB_NAME])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)) 
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @Email = Email FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@Email)) <> (SELECT LTRIM(RTRIM([EMAIL_ADDRESS])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET Email = (SELECT LTRIM(RTRIM([EMAIL_ADDRESS])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @Gender = Gender FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@Gender)) <> (SELECT LTRIM(RTRIM([GENDER])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET Gender = (SELECT LTRIM(RTRIM([GENDER])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END
 SELECT @JoiningDate = JoiningDate FROM [dbo].[EmployeeDetails] WHERE [EmployeeId] = @EmployeeId
 IF(LTRIM(RTRIM(@JoiningDate)) <> (SELECT LTRIM(RTRIM([DATE_OF_JOINING])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId))
    BEGIN
        UPDATE [dbo].[EmployeeDetails] SET JoiningDate = (SELECT LTRIM(RTRIM([DATE_OF_JOINING])) FROM [dbo].[EmployeeMaster] WHERE [EMPLOYEE_NUMBER] = @EmployeeId)
        WHERE EmployeeId = @EmployeeId
    END

 SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #ActiveEmployees

Its working and it updates the columns. now the table has 26196 Records and it takes about 23:56 minutes to execute this stored procedure. In future the records will keep increasing and the execution time will be more.

1. Is there any way to bring down the execution time (suggest some optimization for the query )?
2. Or is there any other way i can sync the two table?
3. Or Is there any way to calculate the execution time for future also and i can set the connection timeout according to that?

Here is my final code

    alter PROCEDURE usp_OracleSyncUpdate
AS
    UPDATE ed
    SET ed.[EmployeeName] = LTRIM(RTRIM(em.FIRST_NAME)),
        ed.EmployeeLastName = LTRIM(RTRIM(em.LAST_NAME)), 
            ed.EmployeeCategoryId = ec.EmployeeCategoryId,
            ed.Email = isnull(ed.Email,em.[EMAIL_ADDRESS]),
            ed.[ContactNo] = isnull(ed.[ContactNo],em.ContactNo),
            ed.Gender = em.Gender,
            ed.JoiningDate = em.[DATE_OF_JOINING]
        FROM dbo.EmployeeDetails ed
        INNER JOIN [EmployeeMaster] em ON ed.EmployeeId = em.[EMPLOYEE_NUMBER]
        INNER JOIN dbo.EmployeeCategory ec ON ec.Category = em.[JOB_NAME]

Thanks @marc_s

Answer 1

Get rid of the RBAR (row-by-agonizing-row) processing - relational databases work in sets of data - use a single set-based UPDATE statement and you're done.

Something along the lines of :

CREATE PROCEDURE usp_OracleSyncUpdate
AS
    CREATE TABLE #ActiveEmployees (
     RowID int IDENTITY(1, 1), 
     [EmployeeId] nvarchar(50),
    )

    -- into the temporary table
    INSERT INTO #ActiveEmployees ([EmployeeId])
      SELECT [EMPLOYEE_NUMBER]
      FROM [dbo].[EmployeeMaster]
      WHERE [EMPLOYEE_NUMBER] IN (SELECT EmployeeId
                              FROM [dbo].[EmployeeDetails])

    UPDATE dbo.EmployeeDetails
    SET ed.EmployeeName = LTRIM(RTRIM(ae.FIRST_NAME)),
        ed.EmployeeLastName = (SELECT LTRIM(RTRIM(ae.LAST_NAME)), 
            ed.EmployeeCategoryId = ec.EmployeeCategoryId,
            ed.Email = ae.Email,
            ed.Gender = ae.Gender,
            ed.JoiningDate = ae.JoiningDate
        FROM dbo.EmployeeDetails ed
        INNER JOIN #ActiveEmployees ae ON ed.EmployeeId = ae.EmployeeId
        INNER JOIN dbo.EmployeeCategory ec ON .......
        WHERE EmployeeId = @EmployeeId

I didn't know how the EmployeeCategory table is linked to the others - you'll need to add that JOIN condition on the INNER JOIN line there.

Also, quite possibly, you could also eliminate that temporary table altogether and just update EmployeeDetails directly from EmployeeMaster as a second step to save memory and processing time.