Insert records and Update existing rows if record exists using Merge in SQL Server

Hi mahesh213,

Use Merge statement and Update if Exist else Insert.

SQL

DECLARE @EmployeeSource TABLE
(
	EId	INT PRIMARY KEY,
	EName VARCHAR(80),
	ECode VARCHAR(50)
)
INSERT INTO @EmployeeSource 
	Values(1,'Name1','Code')
INSERT INTO @EmployeeSource 
	Values(3,'Name3', 'Code3')

DECLARE @EmployeeDestination TABLE
(
	EId	INT PRIMARY KEY,
	EName VARCHAR(50),
	ECode VARCHAR(50),
	Active VARCHAR(50)
)
INSERT INTO @EmployeeDestination 
	Values(1,'Name','Code','Y')
INSERT INTO @EmployeeDestination 
	Values(2,'Name2', 'Code2','N')

MERGE INTO @EmployeeDestination ed
	USING @EmployeeSource es
	ON ed.EId = es.EId
WHEN NOT MATCHED THEN
   INSERT (EId,EName,ECode,Active)
   VALUES (es.EId,es.EName,es.ECode,'Y')
WHEN MATCHED THEN
   UPDATE SET EName = es.EName,ECode = es.ECode;

SELECT * FROM @EmployeeDestination

Output