Here is a sample approach
I have created 2 temp tables "#SampleTable1" and "#SampleTable2" and inserted some data into it as shown below
CREATE TABLE #SampleTable1
(
ID INT,
[Name] VARCHAR(100)
)
CREATE TABLE #SampleTable2
(
ID INT,
[EmpName] VARCHAR(100)
)
INSERT INTO #SampleTable1
SELECT 1, 'James' UNION ALL
SELECT 2, 'Scott' UNION ALL
SELECT 3, 'Rob' UNION ALL
SELECT 4, 'Tom'
INSERT INTO #SampleTable2
SELECT 1, 'James s' UNION ALL
SELECT 2, 'Tom s'
Next I will update the EmpName column in Sample table 2 with the names in sample table 1 based on the ID column.
UPDATE ST2
SET ST2.[EmpName] = ST1.[Name]
FROM #SampleTable1 ST1
INNER JOIN #SampleTable2 ST2 ON ST1.ID = ST2.ID
Next insert records that don't exist in sample table 2 from sample table 1 as shown below
INSERT #SampleTable2
SELECT ST1.ID, ST1.[Name]
FROM #SampleTable1 ST1
LEFT JOIN #SampleTable2 ST2 ON ST1.ID = ST2.ID
WHERE ST2.ID IS NULL