合并数据在实际项目开发过程中，经常有合并数据的需求。这里合并数据的意思是，对于源表A，目标表B，如果A中存在B中不存在则插入记录，如果A中存在B中也存在则更新记录，如果A中不存在B中存在则删除记录。
为了实现这一需求，我们有两种解决方案，一是传统的处理方法，即使用EXISTS谓词，更新和新增分开处理的方式。另一种是使用MERGE语句(SQL Server 2008中新增的功能)。为了演示这一功能，首先我们需要准备测试数据，我们在tempdb临时数据库中新建两个表，源表Customers和目标表Customersstage，然后向这两个表中插入测试数据，如下代码。
USE tempdb;GO-- 合并数据-- 对于源表A，目标表B，如果A中存在B中不存在则插入记录，如果A中存在B中也存在则更新记录，如果A中不存在B中存在则删除记录。-- 准备测试数据IF OBJECT_ID('dbo.Customers','U') IS NOT NULL DROP TABLE dbo.Customers;GOCREATE TABLE dbo.Customers( custid INT NOT NULL, companyname NVARCHAR(30) NOT NULL, phone VARCHAR(30) NOT NULL, ADDRESS NVARCHAR(50) NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY(custid) );INSERT INTO dbo.Customers ( custid, companyname, phone, ADDRESS )VALUES (1,N'cust 1','(111)111-111',N'address 1'), (2,N'cust 2','(222)222-222',N'address 2'), (3,N'cust 3','(333)333-333',N'address 3'), (4,N'cust 4','(444)444-444',N'address 4'), (5,N'cust 5','(555)555-555',N'address 5'); IF OBJECT_ID('dbo.CustomersStage','U') IS NOT NULL DROP TABLE dbo.CustomersStage;GOCREATE TABLE dbo.CustomersStage( custid INT NOT NULL, companyname NVARCHAR(30) NOT NULL, phone VARCHAR(30) NOT NULL, ADDRESS NVARCHAR(50) NOT NULL, CONSTRAINT PK_CustomersStage PRIMARY KEY(custid) );INSERT INTO dbo.CustomersStage ( custid, companyname, phone, ADDRESS )VALUES (2,N'AAAAA','(222)222-222',N'address 2'), (3,N'cust 3','(333)333-333',N'address 3'), (5,N'BBBBB','CCCCC',N'DDDDD'), (6,N'cust 6(new)','(666)666-666',N'address 6'), (7,N'cust 7(new)','(777)777-777',N'address 7');
运行以下代码查看示例数据效果。
SELECT * FROM dbo.Customers;SELECT * FROM dbo.CustomersStage;
查询结果如下。
QQF0xxrLmnLnB/fOspAet5CvJ8x/jFCYTpudIi53yzkIqosNBMBgFIMxyF9lYzJUgu/EaSGlc886Mw0+dAqfXsKQZv3weaCCACUYrh/njF2tCL4wTaEWjhxwC7UbixkV53+6UMtwjaoBVAJw+oL3EEtQnTAr3WKfjRkoRZAJQzbX9+CPudVCdm4d9R++A74RafoR0MWagHQMWz5LrdTsOpzapE54Bedoh8NWY/RB5gstK0WrpPrJEm8/4XBJsg8RyBgcjBsXy0cMUkbf8orOn7udYp+HGTjYAG0Az9q0fj1UdZ4kiQe1SJJEr+H0y86FXriIBsHC6AdxKYWHqXiKeQWXaIfB9k4WADtIDa1QG7hEZ0KPXGQjYMF0A7CUYtaTyDTcouTvaR30qVatHLuDfpJf+B+NxYw0ufdlikYSiptbChpqvoINsf6YH1ubv3AXFIX1YYH1AKgIwS1yN7z3IBaaHKLR+8avWTHrVo0lluog8jB+lzKYvLU4mB9rj9gKgqEkjwAWlUveFGnOpWsFm7VwmZ4QC0AOkJQC+QWEkxBZBJzC8Hnqlf3Y62xrG52pmr1EcJTixTILQBXiE0tjPctnKuFPrcY9McvBe0PijFi/Dm7MpxbH4w+quPIBKqF6HLleF38d9BqkR/p7CI/O7TZMRVKDtbn5vr9uSRJ+oP8X6PDzw2MA/krgZ0NCwCQEZtaGIO5r9ziYH2On1WZWx/IajE+ydNt48otJDaV43WxhaDVIsfIae7oD/qJsuRgfS7XgjFR5cAQv1LY1WMBAGPEphYh5hbm+QcuphSuJ6EWDanF6GJfl6hQS0Ro46wibygea0MJXzevz2cSwlcZUAvAFWJTC2Mw95RbkNRihEF/PB0BtZjI3GKUPuROV1YL9X5HA0PztZLnBBYAoEBsahFibiHPRB1wp7RyLmIQlVp06b6FdGS5oz8+yFJJrh/cUMnmJIWBIc9ZVfScwAIAFIhNLYzB3ONvoop3ufkph7k56S73wXj7Bi4e24SRfld+E1WYTBJvTM+JM05pSSHyZ0MFd7mBsBCOWtQyPMsdAgyhR4j31RCkPsYRZ+NgAbSD2NTCo1Q8hfdEUZ/lroQgtSKWOBsHC6AdxKYWyC08olOhJw6ycbAA2oEftVCt+VgXyC28o1P04yAbBwugHXhQi6POgNzCLzoVeuIgGwcLoB14UAvXlmCZVRiMbJ4jEDA5GHqZiXIBp40T4d0Bv+gU/TjIxsECaAd+1KLx6yPXUlSpNzuLTtGPg2wcLIB2ALVovjc7i07Rj4NsHCyAdgC1aL43O4tO0Y+DbBwsgHYQhFps9sZvSuitOVIL8c0bLnvTHdphYQ0j/dFDdjKFshLxhVEVq4tO1KlOJquB49WQLIYH1AKgw79aDJanxiKxvTKdLG40rxbSq9rc9mYDUAWR1lhYw0A/ffOHTIfsolrToUp1zoc61elktXCpFnbDA2oB0OFfLQTlmFnedpFbZGh+aUupNxtAmZdOWVhDT1/x9g5K5OTWFaxTXYGa1Vl4amHXKtQCoCMotXCVW3DwlVs0uNIqm7zcQi0WQpmpRGqgWnUJNaszpifre6XVcs8pLABARkBqMVieSqaXBtWlgq4W+WIBLntTudvCoge1Vlp1zsIauv5XXlILFMwlQgtVq8v+1KmeojzOHnhZabXa8IBaAHSEOhZ1pIKoFi0EWbUD5vkHLqYkhJVWg5UKRlYLi2AtdFil6sJKq41IhYGsKm9ob6XVqsMDagHQEYRa1JQKilq0E2RrqMUI5pVWQ5YKRlMLu2BdCKe+s4oUGrI+V1q1GB5QC4COANRiYyGpsWoeSS0G/XZ+c6pxQJqJsltptS0W1jD1f3b/QaBAKSn8/NameoWNyZ2sVwtPK61aDQ+oBUCHf7XIH7ZIkiRJXPwmir/xaLw92Exvml2wXmm1NRbWMNIvPmwxpkApyX4TZVs9d6JOdSLZwmSSeGPa4UqrdsMDagHQ4V8tGjE8yx0CDPQPsNJqkIiDBdAOoBbN92ZnYasplVZDRBwsgHYAtWi+NzuLTtGPg2wcLIB24EctVCul1gXUwjs6RT8OsnGwANqBB7U46gzeh753B/yiU/TjIBsHC6AdeFALGAwWjnmOQMDkYOhlJsoFkFt4R6fox0E2DhZAO/CjFo1fH+G+RQjoFP04yMbBAmgHUIvme7Oz6BT9OMjGwQJoB1CL5nuzs+gU/TjIxsECaAdBqEX28g+7xS0oapG9F8Hpg16uz712WFjDSF98+UdGoaxEvdIqubroRJ3qZLIaOF4NyWJ4QC0AOgJQi42F8Qto1xZtXy9Yohb5i0HdPhjc2LmnDCJtsbCGgX7+pieBArEkeymhRfUMNauTyWrhVC2shgfUAqAjALXIbXtlempl1+lMVKPXcprebAAlbrplYQ09fTl+UQIn/yLvOtVl1KzOWIBqYdUo1AKgIyS12F2acZRbZKiwBqV9b6rQ6EqrjllYw5TbydG+ylKnYgMVq4uoWZ0xpifrf6XVKsMDagHQEYpaDJannN63YOPTzMd9C2l9ixorrbbAwhq6/lesnVekUFoirqdUsbrgTJ3qpWQLrRRXs2hhpdWqwwNqAdARiloMRzNRloJRZSbKYahVO0BaO6+4gmb5TFSIgkFUi3FZhct7qYUK1YWVVi32roR2sCnyhvZWWqV4TmIBABKCUovzg+UpF6sh8XA65V9DLUYwr7RqaDIEVFGLyjcuLO4zOL1xoSHrc6VVoucEFgCggH+1GCxPJb01p7kFdw76yC3kmSirlVZbY2ENbehRTJ+MPlJKsgasqzPxu011GllvK63aDQ+oBUCHf7UYcs9b2CUWlNxCXL7SZW8a92+/0mprLKxhpJ9P1AsUCCUH2W+irKoX/KhTnUK2MJnU4kqrdM8pLABARhBqUd/wLHcIMNA/wEqrQcKGRZKwJLHfZc3qgD9ALZrvzc7CSB8rrYaHNHCbTVfL3ELpToEJBNSi+d7sLDpFPway5sBNUQviLuxkyYD9fXbixKji/Dw7c4YdHlZrQYkrV9iJEw20Eyn8qIVqpdS6gFp4R6fox0C2BbVovDpj7MwZhdjMz7OrV2s124hvUcODWhx1Bu8nsHcH/KJT9GMgW0kt6icElJ2W4sqVUQtnz+Ylx4+zJGHHj9s324hvscODWsBgsBCs7n0LYsVmp6FOnSpIRYqrV9mJE+zKFdFD2ecUZ86w+fl8FkvpZ9ZyusckYadOFdKXtPDSJXbsGEuS0RQW/5XfOCs/doxduiQ2sr/P5ufZsWMm9wLA0MtMlAsgt/COTtGPgazFTJQuvtODfs3r9zTmmu9SmNUiu+GRWRqRZbU4PBxFbX6+K9u1rHlpipNZdgsky4cyE4SN317nXgDwoxaNXyXhvkUI6BT9GMjWUQvzxbv1ThupbnYv/ZwG/UuXFP/KkN4gOXVq9DVNMoRcJP2a6UH6VWg2rZgqRLpl1ma62c4OY2zkksE934BaNN+bnUWn6MdAtqpapGEuU4vsM9OoiHz1bbb6Phs8z0qyGaGdHba/b6qYJhZZMnF4OEovlBvzUV74r5CgyI3wqZLBPd+AWjTfm51Fp+jHQLZqBC9VC2aM5nRhMGwmRHBDdV3J1aujiJwF7iwo