DECLARE @a AS TABLE (id int identity(1,1), clave varchar(10), valor varchar(255), propioalfa varchar(20));
DECLARE @d AS TABLE (id int, source varchar(3), clave varchar(10), valor varchar(255), propio varchar(255));
; WITH a AS (SELECT id, clave, valor, propioalfa FROM @a WHERE LEN(propioalfa) < 6 and propioalfa not like 'm%')
MERGE @d as tgt
USING
(
SELECT id, source, clave, valor, propio
FROM
(
SELECT id, 'a' source, clave, valor, propioalfa propio FROM a
) AS X
) AS src (id, source, clave, valor, propio)
ON (tgt.clave = src.clave)
WHEN MATCHED THEN
UPDATE SET tgt.valor = src.propio
WHEN NOT MATCHED THEN
INSERT (id, source, clave, valor, propio)
VALUES (src.id, src.source, src.clave, src.valor, src.propio);
SELECT * FROM @d order by source, clave
-- MERGE
update @a set propioalfa = 'blanco' where clave = 'a1';
update @a set propioalfa = 'amarillo' where clave = 'a2';
update @a set propioalfa = 'rojo' where clave = 'a3';
update @a set propioalfa = 'azul' where clave = 'a4';
update @a set propioalfa = 'verde' where clave = 'a5';
update @a set propioalfa = 'naranja' where clave = 'a6';
update @a set propioalfa = 'morado' where clave = 'a7';
update @a set propioalfa = 'rosa' where clave = 'a8';
update @a set propioalfa = 'negro' where clave = 'a9';
; WITH a AS (SELECT id, clave, valor, propioalfa FROM @a WHERE LEN(propioalfa) > 6)
MERGE @d as tgt
USING
(
SELECT id, source, clave, valor, propio
FROM
(
SELECT id, 'ax' source, clave, valor, propioalfa propio FROM a
) AS X
) AS src (id, source, clave, valor, propio)
ON (tgt.clave = src.clave)
WHEN MATCHED THEN
UPDATE SET tgt.valor = src.propio, tgt.source = src.source
WHEN NOT MATCHED THEN
INSERT (id, source, clave, valor, propio)
VALUES (src.id, src.source + 'y', src.clave, src.valor, src.propio);
--- MERGE TEST
DECLARE @a AS TABLE (id int identity(1,1), clave varchar(10), valor varchar(255), propioalfa varchar(20)); DECLARE @d AS TABLE (id int, source varchar(3), clave varchar(10), valor varchar(255), propio varchar(255));
insert into @a (clave, valor, propioalfa) values ('a1', 'a1', 'mercurio'), ('a2', 'a2', 'venus'), ('a3', 'a3', 'tierra'), ('a4', 'a4', 'marte'), ('a5', 'a5', 'jupiter'), ('a6', 'a6', 'saturno'), ('a7', 'a7', 'urano'), ('a8', 'a8', 'neptuno'), ('a9', 'a9', 'pluton');
; WITH a AS (SELECT id, clave, valor, propioalfa FROM @a WHERE LEN(propioalfa) < 6 and propioalfa not like 'm%')
MERGE @d as tgt
USING ( SELECT id, source, clave, valor, propio FROM ( SELECT id, 'a' source, clave, valor, propioalfa propio FROM a ) AS X ) AS src (id, source, clave, valor, propio) ON (tgt.clave = src.clave)
WHEN MATCHED THEN UPDATE SET tgt.valor = src.propio
WHEN NOT MATCHED THEN INSERT (id, source, clave, valor, propio) VALUES (src.id, src.source, src.clave, src.valor, src.propio);
SELECT * FROM @d order by source, clave -- MERGE
update @a set propioalfa = 'blanco' where clave = 'a1'; update @a set propioalfa = 'amarillo' where clave = 'a2'; update @a set propioalfa = 'rojo' where clave = 'a3'; update @a set propioalfa = 'azul' where clave = 'a4'; update @a set propioalfa = 'verde' where clave = 'a5'; update @a set propioalfa = 'naranja' where clave = 'a6'; update @a set propioalfa = 'morado' where clave = 'a7'; update @a set propioalfa = 'rosa' where clave = 'a8'; update @a set propioalfa = 'negro' where clave = 'a9';
; WITH a AS (SELECT id, clave, valor, propioalfa FROM @a WHERE LEN(propioalfa) > 6) MERGE @d as tgt
USING ( SELECT id, source, clave, valor, propio FROM ( SELECT id, 'ax' source, clave, valor, propioalfa propio FROM a ) AS X ) AS src (id, source, clave, valor, propio) ON (tgt.clave = src.clave)
WHEN MATCHED THEN UPDATE SET tgt.valor = src.propio, tgt.source = src.source
WHEN NOT MATCHED THEN INSERT (id, source, clave, valor, propio) VALUES (src.id, src.source + 'y', src.clave, src.valor, src.propio);
SELECT * FROM @d order by source, clave