{"id":7170,"date":"2019-01-05T10:06:15","date_gmt":"2019-01-05T10:06:15","guid":{"rendered":"https:\/\/dbtut.com\/?p=7170"},"modified":"2019-01-05T21:16:07","modified_gmt":"2019-01-05T21:16:07","slug":"merge-statement-in-sql-servertsql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/","title":{"rendered":"Merge Statement in SQL Server(TSQL)"},"content":{"rendered":"

 <\/p>\n

With the Merge Statement, we can run INSERT, UPDATE, and DELETE commands at one time to synchronize two tables of the same structure.<\/p>\n

First, let’s create source and target tables with the following script, and add a few records to these tables.<\/p>\n

As you can see in the Script, some of the records in the two tables are different. We will synchronize the records in these two tables using the MERGE statement.<\/p>\n

USE [TestDB]\r\nGO\r\nCREATE TABLE [dbo].[MyTableTarget](\r\n[ID] [int] IDENTITY(1,1) NOT NULL,\r\n[Name] [varchar](50) NULL\r\n) ON [PRIMARY]\r\nGO\r\nCREATE TABLE [dbo].[MyTableSource](\r\n[ID] [int] IDENTITY(1,1) NOT NULL,\r\n[Name] [varchar](50) NULL\r\n) ON [PRIMARY]\r\nGO\r\nINSERT INTO [dbo].[MyTableTarget] VALUES ('Nurullah CAKIR'),('Faruk ERDEM')\r\nINSERT INTO [dbo].[MyTableSource] VALUES ('Nurullah CAKIR'),('Faruk ER'),('Hakan GURBASLAR')<\/pre>\n
We then sync the source and target tables with the help of the following script.<\/p>\n
After “WHEN MATCHED THEN<\/strong>“, if the records match, we run the update statement.<\/p>\n
After “WHEN NOT MATCHED BY TARGET THEN<\/strong>“, we perform the insert operation if the record in the source table is not in the target table.<\/p>\n
After “WHEN NOT MATCHED BY SOURCE THEN<\/strong>“, we delete a record that is in the target table from the target table if it is not in the source table.<\/p>\n
With OUTPUT<\/strong> statement, we produce the changes made as output.<\/p>\n
MERGE INTO [dbo].[MyTableTarget] AS target\u00a0\r\n\u00a0\u00a0\u00a0 USING [dbo].[MyTableSource] AS source\u00a0\r\n\u00a0\u00a0\u00a0 ON (target.ID = source.ID)\u00a0\r\n\u00a0\u00a0\u00a0 WHEN MATCHED THEN\r\nUPDATE SET [Name]=Source.[Name]\r\nWHEN NOT MATCHED BY TARGET THEN\r\nINSERT ([Name])\r\nVALUES (source.[Name])\r\nWHEN NOT MATCHED BY SOURCE THEN\r\nDELETE\r\nOUTPUT $action as [action], deleted.[Name], inserted.[Name];<\/pre>\n
<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  With the Merge Statement, we can run INSERT, UPDATE, and DELETE commands at one time to synchronize two tables of the same structure. First, let’s create source and target tables with the following script, and add a few records to these tables. As you can see in the Script, some of the records in …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3,1596],"tags":[],"class_list":["post-7170","post","type-post","status-publish","format-standard","","category-mssql","category-tsql"],"aioseo_notices":[],"yoast_head":"\nMerge Statement in SQL Server(TSQL) - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Merge Statement in SQL Server(TSQL)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Merge Statement in SQL Server(TSQL) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Merge Statement in SQL Server(TSQL)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-05T10:06:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-01-05T21:16:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Merge Statement in SQL Server(TSQL)\",\"datePublished\":\"2019-01-05T10:06:15+00:00\",\"dateModified\":\"2019-01-05T21:16:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/\"},\"wordCount\":174,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png\",\"articleSection\":[\"MSSQL\",\"TSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/\",\"name\":\"Merge Statement in SQL Server(TSQL) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png\",\"datePublished\":\"2019-01-05T10:06:15+00:00\",\"dateModified\":\"2019-01-05T21:16:07+00:00\",\"description\":\"Merge Statement in SQL Server(TSQL)\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png\",\"width\":753,\"height\":481},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Merge Statement in SQL Server(TSQL)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Merge Statement in SQL Server(TSQL) - Database Tutorials","description":"Merge Statement in SQL Server(TSQL)","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/","og_locale":"en_US","og_type":"article","og_title":"Merge Statement in SQL Server(TSQL) - Database Tutorials","og_description":"Merge Statement in SQL Server(TSQL)","og_url":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/","og_site_name":"Database Tutorials","article_published_time":"2019-01-05T10:06:15+00:00","article_modified_time":"2019-01-05T21:16:07+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Merge Statement in SQL Server(TSQL)","datePublished":"2019-01-05T10:06:15+00:00","dateModified":"2019-01-05T21:16:07+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/"},"wordCount":174,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png","articleSection":["MSSQL","TSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/","url":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/","name":"Merge Statement in SQL Server(TSQL) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png","datePublished":"2019-01-05T10:06:15+00:00","dateModified":"2019-01-05T21:16:07+00:00","description":"Merge Statement in SQL Server(TSQL)","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/01\/821.png","width":753,"height":481},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/01\/05\/merge-statement-in-sql-servertsql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Merge Statement in SQL Server(TSQL)"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/7170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=7170"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/7170\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=7170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=7170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=7170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}