﻿-- Merge1
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T X USING SA.S AS Y ON X.I = Y.I WHEN MATCHED THEN UPDATE SET X.A = Y.A, X.B = 'UPDATED' WHEN NOT MATCHED THEN INSERT (I, A, B) VALUES (Y.I, Y.A, 'INSERTED');
/*c5*/SELECT * FROM SA.T; 

-- Merge2
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T USING SA.S ON T.I = S.I WHEN MATCHED THEN UPDATE SET T.A = S.A, T.B = 'UPDATED';
/*c3*/SELECT * FROM SA.T; 

-- Merge3
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T USING SA.S ON T.I = S.I WHEN NOT MATCHED THEN INSERT VALUES (S.I, S.A, 'INSERTED');
/*c5*/SELECT * FROM SA.T; 

-- Merge4
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T USING SA.S ON T.I = S.I WHEN MATCHED THEN UPDATE SET T.A = S.A, T.B = 'UPDATED' WHEN NOT MATCHED THEN INSERT VALUES (S.I, S.A, 'INSERTED'); 
/*c5*/SELECT * FROM SA.T; 

-- Merge5
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T USING (SELECT * FROM SA.S) AS X ON T.I = X.I WHEN MATCHED THEN UPDATE SET T.A = X.A, T.B = 'UPDATED' WHEN NOT MATCHED THEN INSERT VALUES (X.I, X.A, 'INSERTED');  
/*c5*/SELECT * FROM SA.T; 

-- Merge6
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T USING (SELECT I, A, C FROM SA.S) AS X ON T.I = X.I WHEN MATCHED THEN UPDATE SET T.A = X.A, T.B = 'UPDATED' WHEN NOT MATCHED THEN INSERT VALUES (X.I, X.A, 'INSERTED'); 
/*c5*/SELECT * FROM SA.T; 

-- Merge7
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T USING (SELECT * FROM SA.S WHERE I = 4) AS X ON T.I = X.I WHEN MATCHED THEN UPDATE SET T.A = X.A, T.B = 'UPDATED' WHEN NOT MATCHED THEN INSERT VALUES (X.I, X.A, 'INSERTED');  
/*c3*/SELECT * FROM SA.T; 

-- Merge8
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T USING (SELECT * FROM SA.S WHERE I = 3) AS X ON T.I = X.I WHEN MATCHED THEN UPDATE SET T.A = X.A, T.B = 'UPDATED' WHEN NOT MATCHED THEN INSERT VALUES (X.I, X.A, 'INSERTED');  
/*c4*/SELECT * FROM SA.T; 

-- Merge9
DROP SCHEMA SA IF EXISTS CASCADE;
CREATE SCHEMA SA AUTHORIZATION SA;
DROP TABLE SA.T IF EXISTS;
CREATE TABLE SA.T (I IDENTITY, A CHAR(10), B CHAR(10));
INSERT INTO SA.T VALUES ((0, 'A', 'a'),(1, 'B', 'b'), (4, 'C', 'c'));
DROP TABLE SA.S IF EXISTS;
CREATE TABLE SA.S (I IDENTITY, A CHAR(10), B CHAR(10), C CHAR(10));
INSERT INTO SA.S VALUES ((0, 'D', 'd', 'Dd'),(2, 'E', 'e', 'Ee'), (3, 'F', 'f', 'Ff'), (4, 'G', 'g', 'Gg'));
/*c3*/SELECT * FROM SA.T; 
/*c4*/SELECT * FROM SA.S; 
MERGE INTO SA.T USING (SELECT * FROM SA.S WHERE I > 2) AS X ON T.I = X.I WHEN MATCHED THEN UPDATE SET T.A = X.A, T.B = 'UPDATED' WHEN NOT MATCHED THEN INSERT VALUES (X.I, X.A, 'INSERTED'); 
/*c4*/SELECT * FROM SA.T; 