Shulou(Shulou.com)06/02 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.