Merge Table Statement in SQL

Опубликовано: 03 Ноябрь 2024
на канале: Data Science Center
225
14

USE BSE7A;
GO
create table emp1
(
emp_id int primary key,
ename varchar(30) not null,
salary int
);
GO
create table emp2
(
emp_id int primary key,
ename varchar(30) not null,
salary int
);
GO
INSERT INTO EMP1 VALUES(1, 'Saim', 200000);
INSERT INTO EMP1 VALUES(2, 'Arshad', 21000);
INSERT INTO EMP1 VALUES(3, 'Kaleem', 12000);
INSERT INTO EMP1 VALUES(4, 'Jamil', 9800);

INSERT INTO emp2 VALUES(4, 'Rafay', 120000);
INSERT INTO emp2 VALUES(5, 'Aafaq', 21000);
INSERT INTO emp2 VALUES(6, 'Ehsan', 3000);

SELECT * FROM EMP1;
SELECT * FROM EMP2;

MERGE INTO EMP1 USING EMP2
ON (EMP1.EMP_ID = EMP2.EMP_ID)
WHEN MATCHED THEN
UPDATE SET
EMP1.ename = EMP2.ENAME,
EMP1.SALARY = EMP2.SALARY
WHEN NOT MATCHED THEN
INSERT (EMP_ID, ENAME, SALARY)
VALUES (EMP2.EMP_ID, EMP2.ENAME, EMP2.SALARY);

SELECT * FROM EMP1;