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 temp…

Accepted Answer

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.

Update data in SQL Table row by row

Solution

Problem