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

mahesh213
 
on Jul 23, 2022 01:14 AM
371 Views

Hi,

I have 2 tables EmployeeSource,EmployeeDestination

EmployeeSource

EId  EName  ECode

1        Name1    Code

3        Name3    Code3

EmployeeDestination

EId  EName  ECode   Active

1        Name    Code          Y

2        Name2    Code2       Y

Currently my requirement is that based upon source table need to update/insert rows into destination table based upon ECode

this is the my requirement 

EmployeeDestination

EId    EName   ECode    Active

1        Name1    Code          Y

2        Name2    Code2        N

3        Name3     Code3        Y

could you please help me with stored procedure to update, insert data from source table to destination table once user executes the stored procedure

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 23, 2022 06:45 AM

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

EId EName ECode Active
1 Name1 Code Y
2 Name2 Code2 N
3 Name3 Code3 Y