{"id":1602,"date":"2012-12-30T10:00:33","date_gmt":"2012-12-30T15:00:33","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1602"},"modified":"2014-11-13T13:49:37","modified_gmt":"2014-11-13T18:49:37","slug":"a-join-a-day-the-merge-statement","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/","title":{"rendered":"A Join A Day \u2013 The Merge Statement"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nThis is the thirtieth post in my A Join A Day<\/a> series about SQL Server Joins. Make sure to let me know how I am doing or ask your burning join related questions by leaving a comment below.\n<\/p>\n

\nSQL Server 2008 introduced the MERGE<\/span> command. It allows to write an insert, update and delete in a single statement. In this article we are going to look at how this statement works and what MERGE<\/span>-ing has to do with joining.\n<\/p>\n

MERGE Example<\/h3>\n

\nLet's look at a typical reporting scenario. To speed up reporting we have a de-normalized version of the customer data stored in the dbo.CustomerInfo<\/span> table. The following code creates that table and inserts some data into it.\n<\/p>\n

\n[sql]\nIF OBJECT_ID('dbo.CustomerInfo') IS NOT NULL DROP TABLE dbo.CustomerInfo;<\/p>\n

CREATE TABLE [dbo].[CustomerInfo] (
\n [CustomerID] [int] PRIMARY KEY CLUSTERED,
\n [AccountNumber] [varchar](10) NOT NULL,
\n [PersonType] [nchar](2) NOT NULL,
\n [NameStyle] [dbo].[NameStyle] NOT NULL,
\n [Title] [nvarchar](8) NULL,
\n [FirstName] [dbo].[Name] NULL,
\n [MiddleName] [dbo].[Name] NULL,
\n [LastName] [dbo].[Name] NOT NULL,
\n [Suffix] [nvarchar](10) NULL,
\n [Store] [dbo].[Name] NOT NULL,
\n [Teritory] [dbo].[Name] NOT NULL,
\n [CountryRegionCode] [nvarchar](3) NOT NULL,
\n [SalesYTD] [money] NOT NULL,
\n [SalesLastYear] [money] NOT NULL,
\n [ModifiedDate] [datetime] DEFAULT GETDATE()
\n );
\n GO<\/p>\n

INSERT INTO dbo.CustomerInfo
\n (
\n CustomerID,
\n AccountNumber,
\n PersonType,
\n NameStyle,
\n Title,
\n MiddleName,
\n LastName,
\n Suffix,
\n Store,
\n Teritory,
\n CountryRegionCode,
\n SalesYTD,
\n SalesLastYear
\n )
\n SELECT CASE WHEN c.CustomerID % 10 = 0 THEN c.CustomerID - 29000 ELSE c.CustomerID END,
\n c.AccountNumber,
\n p.PersonType,
\n p.NameStyle,
\n p.Title,
\n p.MiddleName,
\n p.LastName,
\n p.Suffix,
\n s.Name AS Store,
\n st.Name AS Teritory,
\n st.CountryRegionCode,
\n st.SalesYTD,
\n st.SalesLastYear
\n FROM Sales.Customer AS c
\n JOIN Person.Person AS p
\n ON c.PersonID = p.BusinessEntityID
\n JOIN Sales.Store AS s
\n ON c.StoreID = s.BusinessEntityID
\n JOIN Sales.SalesTerritory AS st
\n ON c.TerritoryID = st.TerritoryID;<\/p>\n

SELECT * FROM dbo.CustomerInfo;
\n[\/sql]\n<\/p><\/div>\n

\nThe customer information is retrieved joining several tables together.\n<\/p>\n

\nThe task now is to write a query that can bring the contents of this table up to date each time when executed. This query can then be scheduled to be executed at regular intervals.\n<\/p>\n

\nThe MERGE<\/span> statement does provide this functionality in one simple construct:\n<\/p>\n

\n[sql]\nMERGE dbo.CustomerInfo AS ci
\n USING Sales.Customer AS c
\n JOIN Person.Person AS p
\n ON c.PersonID = p.BusinessEntityID
\n JOIN Sales.Store AS s
\n ON c.StoreID = s.BusinessEntityID
\n JOIN Sales.SalesTerritory AS st
\n ON c.TerritoryID = st.TerritoryID
\n ON ci.CustomerID = c.CustomerID
\n WHEN MATCHED THEN
\n UPDATE
\n SET FirstName = p.FirstName,
\n ModifiedDate = DEFAULT
\n WHEN NOT MATCHED THEN
\n INSERT (
\n CustomerID,
\n AccountNumber,
\n PersonType,
\n NameStyle,
\n Title,
\n FirstName,
\n MiddleName,
\n LastName,
\n Suffix,
\n Store,
\n Teritory,
\n CountryRegionCode,
\n SalesYTD,
\n SalesLastYear
\n )
\n VALUES
\n (
\n c.CustomerID,
\n c.AccountNumber,
\n p.PersonType,
\n p.NameStyle,
\n p.Title,
\n p.FirstName,
\n p.MiddleName,
\n p.LastName,
\n p.Suffix,
\n s.Name,
\n st.Name,
\n st.CountryRegionCode,
\n st.SalesYTD,
\n st.SalesLastYear
\n )
\n WHEN NOT MATCHED BY SOURCE
\n THEN
\n DELETE
\n OUTPUT $action,ISNULL(INSERTED.CustomerId, DELETED.CustomerId);
\n[\/sql]\n<\/div>\n

\nNow you probably are asking yourself right now what my breakfast was laced with getting me to call this monster statement \"simple\", but once you break it apart into its pieces it looks a lot like separate update, insert and delete statements and with that it becomes a lot easier to follow.\n<\/p>\n

\nThe OUTPUT<\/span> clause in the example shows a new pseudo column: $action<\/span>. It was introduced together with MERGE<\/span> and it is valued either \"insert\", \"update\" or \"delete\" dependent on which action affected the current row. With this output we get a record of what happened to each row. You could include the other columns used in the MERGE<\/span> statement too, if you want to create a complete change data log.\n<\/p>\n

\nThe above create table script had deliberately a few defects. Some of the records where inserted with a significantly to small CustomerId<\/span> value and all of them did not have the first name valued. Executing the above MERGE<\/span> statement automatically deletes the rows in the target table that do not exist in the source table, it inserts new rows and updates all existing rows. So, after it's execution we expect the data defects to be all gone.\n<\/p>\n

\nThe output of the above MERGE<\/span> statement looks like this:\n<\/p>\n

\n\"merge<\/a>\n<\/p>\n

\nYou can see in the output that some rows were deleted, some were inserted and some updated. Take a look at the dbo.CustomerInfo<\/span> yourself to confirm that it is now completely in sync with the source data.\n<\/p>\n

MERGE Syntax<\/h3>\n

\nThe MERGE<\/span> statement has several sections. The first section specifies the tables involved. The target table is mentioned right after the MERGE<\/span> keyword. After the USING<\/span> keyword the source tables are specified. The final ON<\/span> clause tells SQL Server how to determine if rows are a match or not.\n<\/p>\n

The next three sections are the insert, update and delete sections. They each get identified by a WHEN<\/span> clause. WHEN MATCHED<\/span> starts the section were an update statement can be specified. WHEN NOT MATCHED<\/span> allows to specify an insert statement for new rows. WHEN NOT MATCHED BY SOURCE<\/span> finally provides a way to delete orphaned rows.
\nThe syntax of all three sections is very close to the stand alone INSERT<\/span>, UPDATE<\/span> and DELETE<\/span> statements, you just leave out the target table name. So, UPDATE dbo.table SET<\/span> becomes UPDATE SET<\/span>. Instead of INSERT dbo.table(column, list)<\/span> you write INSERT (column, list)<\/span>. The DELETE<\/span> finally gets reduced to just the single keyword.\n<\/p>\n

\nAll three sections are optional. However, at least one needs to be part of every MERGE<\/span> construct, as it otherwise would just be a NOOP. All three operations can also be independently further restricted by supplying an additional condition after the WHEN<\/span> clause linked with the AND<\/span> keyword.\n<\/p>\n

\nThe last section is the OUTPUT<\/span> clause that works like the OUTPUT<\/span> clause in any INSERT<\/span>, UPDATE<\/span> or DELETE<\/span> statement.\n<\/p>\n

\nThe complete description of the MERGE<\/span> statement you can find here: http:\/\/msdn.microsoft.com\/en-us\/library\/bb510625(v=sql.100).aspx<\/a>\n<\/p>\n

MERGE Operator<\/h3>\n

\nWhen looking at the execution plan for a MERGE<\/span> statement two things are of interest. To demonstrate them let's first look at the execution plan for the insert<\/span> statement above that we used to put the initial set of data into out dbo.CustomerInfo<\/span> table:\n<\/p>\n

\n\"insert<\/a>\n<\/p>\n

\nAs always, you can click on the image to get a larger version.\n<\/p>\n

\nThe execution plan for the MERGE<\/span> statement looks like this:\n<\/p>\n

\n\"merge<\/a>\n<\/p>\n

\nThe execution plan sub-tree that is responsible to retrieve the data from the four tables can be found pretty much unchanged in the execution plan for the MERGE<\/span> statement as well. However there is now an additional join. That additional join shows up as a Full Outer Join operator that joins the result of above sub-tree with the target table (dbo.CustomerInfo<\/span>).\n<\/p>\n

\nThat Full Outer Join operator is actually what makes the identification of matching or missing rows possible. The Merge operator uses the information coming from this join to decide which operation to execute. The Merge operator is actually a single new operator that looks similar the known Insert, Update or Delete operators. It just is able to execute all three operations at once.\n<\/p>\n

\nBe aware that MERGE<\/span> is not able to handle more than one row on the source side matching a single target side row. The same is true for the other direction too. Because of that MERGE<\/span> is best suited to handle comparison of tables on a primary key column.\n<\/p>\n

Summary<\/h3>\n

\nThe MERGE<\/span> statement gives us a powerful way to encapsulate the complex logic necessary to sync one table with another. Under the cover the Merge operator can execute inserts, updates and deletes against the target table. Which action to take it decides base on the result of a full outer join of the target table and the source table(s).\n<\/p>\n

A Join A Day<\/h3>\n

\nThis post is part of my December 2012 \"A Join A Day\" blog post series. You can find the table of contents with all posts published so far in the introductory post: A Join A Day \u2013 Introduction<\/a>. Check back there frequently throughout the month.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[28,29,5,27,23],"tags":[],"class_list":["post-1602","post","type-post","status-publish","format-standard","hentry","category-a-join-a-day","category-fundamentals","category-general","category-series","category-t-sql-statements"],"acf":[],"yoast_head":"\nA Join A Day \u2013 The Merge Statement - sqlity.net<\/title>\n<meta name=\"description\" content=\"SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A Join A Day \u2013 The Merge Statement - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-30T15:00:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:49:37+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-result.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"A Join A Day \u2013 The Merge Statement\",\"datePublished\":\"2012-12-30T15:00:33+00:00\",\"dateModified\":\"2014-11-13T18:49:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/\"},\"wordCount\":1423,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/merge-result.jpg\",\"articleSection\":[\"A Join A Day\",\"Fundamentals\",\"General\",\"Series\",\"T-SQL Statements\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/\",\"name\":\"A Join A Day \u2013 The Merge Statement - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/merge-result.jpg\",\"datePublished\":\"2012-12-30T15:00:33+00:00\",\"dateModified\":\"2014-11-13T18:49:37+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/merge-result.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/merge-result.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1602\\\/a-join-a-day-the-merge-statement\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A Join A Day \u2013 The Merge Statement\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A Join A Day \u2013 The Merge Statement - sqlity.net","description":"SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.","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:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/","og_locale":"en_US","og_type":"article","og_title":"A Join A Day \u2013 The Merge Statement - sqlity.net","og_description":"SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.","og_url":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-12-30T15:00:33+00:00","article_modified_time":"2014-11-13T18:49:37+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-result.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"A Join A Day \u2013 The Merge Statement","datePublished":"2012-12-30T15:00:33+00:00","dateModified":"2014-11-13T18:49:37+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/"},"wordCount":1423,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-result.jpg","articleSection":["A Join A Day","Fundamentals","General","Series","T-SQL Statements"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/","url":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/","name":"A Join A Day \u2013 The Merge Statement - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-result.jpg","datePublished":"2012-12-30T15:00:33+00:00","dateModified":"2014-11-13T18:49:37+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-result.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/merge-result.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1602\/a-join-a-day-the-merge-statement\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"A Join A Day \u2013 The Merge Statement"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-pQ","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1602","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1602"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1602\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}