The usage of MERGE statement in SQL2008
This article introduces the knowledge about "MERGE statement usage in SQL2008". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!
SQL Server 2008 supports MERGE statements, which can be used as follows:
--Source table
CREATE TABLE test_from (id INT, val VARCHAR(20));
--Target list
CREATE TABLE test_to (id INT, val VARCHAR(20));
--Insert source table
INSERT INTO test_from VALUES (1, 'A');
INSERT INTO test_from VALUES (2, 'B');
--Merge source table to target table
MERGE test_to USING test_from
ON ( test_to.id = test_from.id ) --if id is the same
WHEN MATCHED THEN UPDATE SET test_to.val = test_from.val --When matched, update
WHEN NOT MATCHED THEN INSERT VALUES(test_from.id, test_from.val)
WHEN NOT MATCHED BY SOURCE THEN Deleted; --Destination table yes, source table no, destination table this data deleted.
--First check of target table data.
SELECT * FROM test_to;
id val
----------- --------------------
1 A
2 B
--Update source table
UPDATE test_from SET val = 'A2' WHERE id = 1;
--Delete source table
DELETE FROM test_from WHERE id = 2;
--Insert source table
INSERT INTO test_from VALUES (3, 'C');
--Merge source table to target table
MERGE test_to USING test_from
ON ( test_to.id = test_from.id ) --if id is the same
WHEN MATCHED THEN UPDATE SET test_to.val = test_from.val --When matched, update
WHEN NOT MATCHED THEN INSERT VALUES(test_from.id, test_from.val)
WHEN NOT MATCHED BY SOURCE THEN Deleted; --Destination table yes, source table no, destination table this data deleted.
--Check the target table data again.
SELECT * FROM test_to;
id val
----------- --------------------
1 A2
3 C
"MERGE statement usage in SQL2008" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!
