{"id":95308,"date":"2023-01-17T14:31:31","date_gmt":"2023-01-17T14:31:31","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95308"},"modified":"2023-01-17T15:13:27","modified_gmt":"2023-01-17T15:13:27","slug":"manage-data-over-time-with-sql-server-merge-statement","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/manage-data-over-time-with-sql-server-merge-statement\/","title":{"rendered":"Manage Data Over Time with SQL Server MERGE Statement"},"content":{"rendered":"<p style=\"margin: 0in; font-family: Calibri; font-size: 11.0pt;\"><p><strong>This article is part of Greg Larsen's continuing series on Learning T-SQL. To see all the items in the series, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/t-sql-coding-basics\/\">click here<\/a>.<\/strong><\/p>\n<\/p>\n<h2>The basic MERGE statement<\/h2>\n<p>The <code>MERGE<\/code> statement was introduced with SQL Server 2008. It provides a single statement to perform <code>UPDATE<\/code>, <code>INSERT<\/code> and\/or <code>DELETE<\/code> operations against a target table. The syntax for the basic <code>MERGE<\/code> statement that will be discussed, in this article, can be found in Figure 1:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">MERGE\r\n[ INTO ] &lt;target_table&gt;\r\n\r\nUSING &lt;table_source&gt; [ [ AS ] table_alias ]\r\n   ON &lt;merge_search_condition&gt;\r\n\r\n[ WHEN MATCHED [ AND &lt;clause_search_condition&gt; ]\r\n  THEN &lt;merge_matched&gt; ] [ ...n ]\r\n\r\n[ WHEN NOT MATCHED [ BY TARGET ] [ AND &lt;clause_search_condition&gt; ]\r\n  THEN &lt;merge_not_matched&gt; ]\r\n\r\n[ WHEN NOT MATCHED BY SOURCE [ AND &lt;clause_search_condition&gt; ]\r\n  THEN &lt;merge_matched&gt; ] [ ...n ];\r\n<\/pre>\n<p><strong>Figure 1: Basic Merge Statement <\/strong><\/p>\n<p>Where:<\/p>\n<ul>\n<li><em>target_table \u2013 <\/em>identifies the table or view from which rows will be matched, so the appropriate update, insert, or delete actions can be performed based on the matching criteria.<\/li>\n<li><em>table_source &#8211;<\/em>identifies the data source rows for which <em>target_table <\/em>rows are matched.<\/li>\n<li><em>merge_search_condition \u2013 <\/em>identifies the join criteria for matching <em>target_table <\/em>rows, with the <em>source_table <\/em>rows.<\/li>\n<li><em>merge_matched \u2013 <\/em>identifies either an <code>UPDATE<\/code> or <code>DELETE<\/code> statement to issue based on matching criteria.<\/li>\n<li><em>merged_no_matched \u2013 <\/em>identifies the <code>INSERT<\/code> statement to issue for each row not matched.<\/li>\n<li><em>clause_search_condition \u2013 <\/em>identifies any valid search condition.<\/li>\n<\/ul>\n<p>For the complete syntax of the <code>MERGE<\/code> statement refer to the Microsoft documentation that can be found <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/merge-transact-sql?view=sql-server-ver16\">here<\/a>.<\/p>\n<p>To better understand how the <code>MERGE<\/code> statement maintains a <em>target<\/em> table, a few merge examples will be provided, in the sections below. But first some test data needs to be created.<\/p>\n<h1>Creating Initial Test Data<\/h1>\n<p>To show how the <code>MERGE<\/code> statement works a new database will be created. The new database will contain tables that I will use as a <em>target <\/em>and <em>source<\/em> for the examples, which will be populated with the initial test data. The examples in this article will show how a fictious manufacturing company named <em>Widget Magic <\/em>keeps this data synchronized using different <code>MERGE<\/code> examples.<\/p>\n<p>The code in Listing 1 is used to create a database, the <em>target <\/em>and <em>source <\/em>table, and populates these tables with some initial rows of data. Note if you don\u2019t want to create a new database the <em>source<\/em> and <em>target <\/em>tables can be created in the <code>tempdb<\/code> database.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- Create repository for products\r\nCREATE DATABASE MergeDB\r\nGO\r\n\r\nUSE MergeDB;\r\n\r\nSET NOCOUNT ON;\r\n\r\nCREATE TABLE dbo.ProductInventory (\r\n   ID int identity, \r\n   ProductName varchar(20),\r\n   Qty int);\r\n\r\nCREATE TABLE dbo.ProductRun(\r\n   RunNum int,\r\n   ProductName varchar(20), \r\n   Qty int);\r\n-- Product Run #1\r\n\r\nUSE MergeDB;\r\nGO\r\n\r\nINSERT INTO dbo.ProductRun (RunNum, ProductName, Qty) VALUES\r\n   (1,'Widget Basic',5), \r\n   (1,'Widget Pro',5);\r\nGO\r\n\r\nINSERT INTO dbo.ProductInventory (ProductName, Qty) VALUES\r\n   ('Widget Basic',5),\r\n   ('Widget Pro', 5);\r\nGO<\/pre>\n<p><strong>Listing 1: Create repository for products<\/strong><\/p>\n<p>In Listing 1 two tables are created: <code>dbo.ProductInventory<\/code> and <code>dbo.ProductRun<\/code><em>. <\/em> The <code>dbo.ProductInventory<\/code> table tracks the inventory of products created by <em>Widget Magic <\/em>company. Whereas, the <code>dbo.ProductRun<\/code> table will be used to keep track of the products produced from each product run cycle. The rows of data inserted into these two tables represents the products produced from the first product run of <em>widget <\/em>products. During that first product run 5 <em>Widget Basic <\/em>and 5 <em>Widget Pro <\/em>items were produced.<\/p>\n<p>For the purpose of this article the <code>dbo.ProductInventory<\/code> table will be used as the <em>target <\/em>table of the <code>MERGE<\/code> examples. Whereas, records from the <code>dbo.ProductRun<\/code> table will be used as the <em>source<\/em> table records.<\/p>\n<h1>Merge Example #1 \u2013 Inserting\/Updating Using MERGE Statement<\/h1>\n<p>In order to show how the <code>MERGE<\/code> statement works to maintain the <code>dbo.ProductInventory<\/code> table, the <em>Widget Magic <\/em>company first needs to have a second product run cycle. The second run produced 5 more <em>Widget Pro <\/em>items and introduced a new product called <em>Widget Super Pro, <\/em>of which 5 are produced<\/p>\n<p>The code in Listing 2 inserts two new rows into the <code>dbo.ProductRun<\/code> table to represent the new products produced by this second product run.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- Product Run #2\r\nUSE MergeDB;\r\nGO\r\n\r\nINSERT INTO dbo.ProductRun (RunNum, ProductName, Qty) VALUES\r\n   (2, 'Widget Pro',5),\r\n   (2, 'Widget Super Pro',5);\r\nGO<\/pre>\n<p><strong>Listing 2: Inserting Production Run #2 Rows<\/strong><\/p>\n<p>To update the <code>dbo.ProductInventory<\/code> table, based on the products manufactured during the second product run, the <code>MERGE<\/code> statement in Listing 3 will be run.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- Merge Example #1 \r\nUSE MergeDB;\r\nGO\r\n\r\nMERGE dbo.ProductInventory AS T  \r\nUSING (SELECT * FROM dbo.ProductRun WHERE RunNum = 2) AS S \r\nON T.ProductName = S.ProductName \r\n-- Perform UPDATE when rows are matched \r\n-- between source and target\r\nWHEN MATCHED THEN \r\n   UPDATE SET T.Qty = T.Qty + S.Qty\r\n-- Perform INSERT when rows are not matched \r\n--between source and target\r\nWHEN NOT MATCHED BY TARGET THEN \r\n   INSERT (ProductName,Qty) VALUES (S.ProductName, S.Qty);\r\n\r\n-- Review Inventory\r\nSELECT * FROM dbo.ProductInventory;\r\nGO <\/pre>\n<p><strong>Listing 3: Merge Example #2 \u2013 Inserting\/Updating <em>ProductInventory <\/em>table<\/strong><\/p>\n<p>Report 1 shows the rows in the <code>dbo.ProductInventory<\/code> table after the <code>MERGE<\/code> statement was executed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"214\" height=\"93\" class=\"wp-image-95309\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/01\/word-image-95308-1.png\" \/><\/p>\n<p><strong>Report 2: Output of SELECT statement in Listing 5<\/strong><\/p>\n<p>The <code>MERGE<\/code> statement in Listing 3 compares the <code>dbo.ProductInventory<\/code> records<em>, <\/em>with the <em>widgets <\/em>products produced during the second product run. The <code>dbo.ProductInventory<\/code> table is the <em>target <\/em>table, and the rows returned from the subquery \u201c<code>SELECT * FROM dbo.ProductRun WHERE ProductRun = 2<\/code>\u201d identifies the <em>source<\/em> rows. The <em>target <\/em>and <em>source <\/em>rows are joined based on the <em>merge_search_condition <\/em>specified with the \u201c<code>ON<\/code>\u201d clause, which in this case was \u201c<code>T.ProductName = S.ProductName<\/code><em>\u201d.<\/em><\/p>\n<p>A word of caution here, <em>target <\/em>table columns should only be compared with <em>source <\/em>columns. Do not use any <em>target <\/em>table columns to filter out rows as part of the \u201cON\u201d clause. Doing so could produce unexpected or incorrect results.<\/p>\n<p>When rows are matched between <em>target <\/em>and <em>source <\/em>rows, SQL Server assigns a matching condition for each row, based on the <em>merge_search_condition<\/em>. When this condition equates to true, the <em>source<\/em> row is known as a <code>MATCHED<\/code> with a <em>target <\/em>row. When the merge search condition is false the <em>source <\/em>table row is considered \u201c<code>NOT MATCHED\u201d<\/code>. <code>NOT MATCHED<\/code> then is be broken into two different conditions \u201c<code>NOT MATCHED BY TARGET<\/code>\u201d and \u201c<code>NOT MATCHED BY SOURCE<\/code>\u201d.<\/p>\n<p>The \u201c<code>NOT MATCHED BY TARGET<\/code>\u201d in this example means a row exists in the source table that did not match a row in table based on the join criteria. Whereas the \u201c<code>NOT MATCHED BY SOURCE<\/code>\u201d condition means that a row exists in the target table that has no matching rows in the source table (look at Example 2 for to see a \u201c<code>NOT MATCHED BY SOURCE<\/code>\u201d example). Additionally, when both source and target table comparison columns contain <code>NULL<\/code> values, the matching value is considered \u201c<code>NOT MATCHED<\/code>\u201d for both target and source tables.<\/p>\n<p>For each row in <em>source <\/em>table from second product run, that got a <code>MATCHED<\/code> condition an <code>UPDATE<\/code> operation against the <em>target <\/em>table was performed. The <code>UPDATE<\/code> operation increases the inventory value of the <code>Qty<\/code> column, on the matched <em>target <\/em>row, based on the <code>Qty<\/code> column value, of the matching row in the <em>source <\/em>table.<\/p>\n<p>An <code>INSERT<\/code> operation was performed for each <em>source <\/em>row, from the second product run that got a <code>NOT MATCHED BY TARGET<\/code> condition. The <code>INSERT<\/code> operation uses the <em>source <\/em>table\u2019s <code>ProductName<\/code> and <code>Qty<\/code> values to insert a new rows into the <em>target <\/em>table for each new product produced.<\/p>\n<p>Report 2 shows that 5 more <em>Widget Pro<\/em> and <em>Widget Super Pro <\/em>items were added to the <code>dbo.ProductInventory<\/code> table. This <code>MERGE<\/code> statement showed how to insert and update rows into the <em>target<\/em> table, based on a matching condition with the <em>source<\/em> table. The <code>MERGE<\/code> statement can also perform <code>DELETE<\/code> statements.<\/p>\n<h1>Merge Example #2 \u2013 Inserting\/Updating\/Deleting using MERGE <em>Statement<\/em><\/h1>\n<p>The <em>Widget Magic <\/em>company so far has had two different product runs of their <em>widget<\/em> products. The <em>Widget Basic <\/em>product has not been well received, as the product has been demonstrated to potential customers. Therefore management has decided that this product should be removed from the inventory, once the next product run has completed.<\/p>\n<p>For the third product run, only the <em>Widget Pro, <\/em>and <em>Widget Super Pro <\/em>items are produced. The code in Listing 6 inserts these products from product run #3 into the <code>dbo.ProductRun<\/code> table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- Product Run #3\r\nUSE MergeDB;\r\nGO\r\n\r\nINSERT INTO dbo.ProductRun (RunNum, ProductName, Qty) VALUES\r\n   (3, 'Widget Pro',100),\r\n   (3, 'Widget Super Pro',50);\r\nGO<\/pre>\n<p><strong>Listing 4: Inserting Product Run #3 Rows<\/strong><\/p>\n<p>To show how the <code>MERGE<\/code> statement can insert, update, and delete rows from a <em>target <\/em>table, the code in Listing 5 merges will be run.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\"><a id=\"post-95308-_Hlk121377375\"><\/a>-- Merge Example #2 \r\nUSE MergeDB;\r\nGO\r\n\r\nMERGE dbo.ProductInventory AS T  \r\nUSING (SELECT * FROM dbo.ProductRun WHERE RunNum = 3) AS S \r\nON T.ProductName = S.ProductName \r\nAND S.RunNum = 3\r\n-- Perform UPDATE when both target and source MATCHED \r\nWHEN MATCHED THEN \r\n   UPDATE SET T.Qty = T.Qty + S.Qty\r\n-- Perform INSERT when NOT MATCHED BY SOURCE\r\nWHEN NOT MATCHED BY TARGET THEN \r\n   INSERT (ProductName,Qty) VALUES (S.ProductName, S.Qty)\r\n-- Perform DELETE when NOT MATCHED BY SOURCE\r\nWHEN NOT MATCHED BY SOURCE THEN\r\n   DELETE;\r\n\r\n-- Review Inventory\r\nSELECT * FROM dbo.ProductInventory;\r\nGO<\/pre>\n<p><strong>Listing 5: Inserting\/Updating\/Deleting <em>ProductionInventory <\/em>table<\/strong><\/p>\n<p>The code in Listing 5 now contains a third <code>WHEN<\/code> condition, \u201c<code>NOT MATCHED BY SOURCE<\/code>\u201d. This <code>WHEN<\/code> condition is followed when there is a row in the target table that does not exist in the source table. This is the opposite of the insert where there were rows that existed in the source table that did not exist in the target. When no matching rows are found a <code>DELETE<\/code> statement is executed. Or in this case, the discontinued product name \u201c<em>Widget Basic<\/em>\u201d will be deleted from the<em> target <\/em>table.<\/p>\n<p>Additionally, when <em>target <\/em>and <em>source <\/em>rows are <code>MATCHED<\/code> the inventory <code>Qty<\/code> column of the <em>target <\/em>table is updated, based on the number of <em>widgets<\/em> produced during the third product run. The <code>NOT MATCHED BY TARGET<\/code> condition is not fired because no new products were introduced during the third product run. The resulting rows in the <code>dbo.ProductInventory<\/code> after Listing 5 is executed, can be seen in Report 3.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"209\" height=\"71\" class=\"wp-image-95310\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/01\/word-image-95308-2.png\" \/><\/p>\n<p><strong>Report 3: Output from SELECT statement in Listing 5.<\/strong><\/p>\n<p>The <code>MERGE<\/code> example in Listing 5 had single <code>MATCHED<\/code>, <code>NOT MATCHED BY TARGET<\/code> and <code>NOT MATCHED BY SOURCE<\/code> clauses. The <code>MERGE<\/code> statement can support multiple <code>MATCH<\/code> and <code>NOT MATCHED<\/code> conditions, as well be shown in the next example.<\/p>\n<h1>MERGE Example 3 \u2013 Multiple MATCH and NOT MATCH conditions<\/h1>\n<p>To show how multiple <code>MATCH<\/code> and <code>NOT MATCH<\/code> conditions can be used on a <code>MERGE<\/code> statement, some new data management requirements are needed.<\/p>\n<p>The first new requirement is a soft delete. To implement the soft delete the <code>dbo.ProductInventory<\/code> table needs to be modified to contain a soft delete flag.. Plus the <em>Widget Basic<\/em> inventory row that was deleted in the last <code>MERGE<\/code> example, will need to be re-inserted into the changed <code>dbo.ProductInventory<\/code> table. The code in Listing 6 will implement these two different changes to the <em>target <\/em>table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- Implement Soft Delete Requirements\r\nUSE MergeDB\r\nGO\r\n\r\n-- Add Soft Delete column\r\nALTER TABLE dbo.ProductInventory ADD\r\n\tToBeDeleted char(1) NULL\r\nGO\r\n\r\n-- Re-introduce <em>Widget<\/em> <em>Basic <\/em>product\r\nINSERT INTO dbo.ProductInventory (ProductName, Qty, ToBeDeleted) VALUES\r\n   ('Widget Basic',5,'Y')\r\nGO<\/pre>\n<p><strong>Listing 6: Changes needed to implement new soft delete requirements <\/strong><\/p>\n<p>The second requirement is to perform a hard delete of a <code>dbo.ProductInventory<\/code> record, if a <code>dbo.ProductRun<\/code> record is found to have a <code>Qty<\/code> value of zero (0).<\/p>\n<p>For the fourth product run only the <em>Widget Pro <\/em>items is produced. Additional a <em>Widget Super Pro <\/em>record with a <code>Qty<\/code> value of 0 will be added to the <code>dbo.ProductRun<\/code> table. This record was added so a hard delete of the <code>dbo.ProductInventory<\/code> record could be performed. Listing 7 contains the <code>INSERT<\/code> statements for these two products, for the 4<sup>th<\/sup> product run.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">-- Product Run #4\r\nUSE MergeDB;\r\nGO\r\n\r\nINSERT INTO dbo.ProductRun (RunNum, ProductName, Qty) VALUES\r\n   (4, 'Widget Pro',100),\r\n   (4, 'Widget Super Pro',0);\r\nGO<\/pre>\n<p><strong>Listing 7: Product Run #4 <\/strong><\/p>\n<p>To implement the two new data processing requirements the <code>MERGE<\/code> statement in Listing 8 will be used. This statement contains multiple <code>MATCHED<\/code> and <code>NOT MATCHED BY<\/code> merge options.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE MergeDB;\r\nGO\r\n\r\nMERGE dbo.ProductInventory AS T  \r\nUSING (SELECT * FROM dbo.ProductRun  WHERE RunNum = 4) AS S \r\nON T.ProductName = S.ProductName \r\nWHEN MATCHED and S.Qty = 0 THEN \r\n   DELETE\r\nWHEN MATCHED THEN\r\n   UPDATE SET T.Qty = T.Qty + S.Qty\r\nWHEN NOT MATCHED BY TARGET AND S.QTY &lt;&gt; 0 THEN \r\n   INSERT (ProductName,Qty) VALUES (S.ProductName, S.Qty)\r\nWHEN NOT MATCHED BY SOURCE and T.ToBeDeleted = 'Y' THEN\r\n   DELETE\r\nWHEN NOT MATCHED BY SOURCE THEN\r\n   UPDATE SET T.ToBeDeleted = 'Y';\r\n\r\n-- Review Inventory\r\nSELECT * FROM dbo.ProductInventory;\r\nGO <\/pre>\n<p><strong>Listing 8: Merge data with multiple MATCH and NOT MATCHED conditions<\/strong><\/p>\n<p>The SELECT statement at the bottom of Listing 8 created the output in Report 4.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"301\" height=\"45\" class=\"wp-image-95311\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/01\/word-image-95308-3.png\" \/><\/p>\n<p><strong>Report 4: Output from final SELECT statement in Listing 8.<\/strong><\/p>\n<p>When there are 2 <code>MATCHED BY<\/code> clauses the first match clause requires an <code>AND<\/code> condition. The second <code>MATCHED BY<\/code> condition is only applied if the first <code>MATCHED BY<\/code> condition is not met. Additionally, when there are 2 <code>MATCHED BY<\/code> clauses one must perform an <code>UPDATE<\/code> and the other a <code>DELETE<\/code>.<\/p>\n<h1>Gotcha\u2019s<\/h1>\n<p>Here are a few things that you might run into while using the <code>MERGE<\/code> statement. The first two were already mentioned, but are worth repeating.<\/p>\n<ul>\n<li>Unexpected results can occur if the <code>ON<\/code> clause tries to filter rows based on <em>target <\/em>columns.<\/li>\n<li>If no rows are returned from the <em>source <\/em>table when joining the <em>target <\/em>and <em>source <\/em>then the <code>UPDATE<\/code> or <code>DELETE<\/code> cannot reference a source column because there are no matching rows between <em>target <\/em>and <em>source<\/em>.<\/li>\n<li>Therefore, care needs to be taken when joining <em>target <\/em>and <em>source <\/em>rows, so multiple <em>source<\/em> rows are not matched with a single <em>target <\/em>row. This usually means that the join between the <em>target<\/em> and <em>source<\/em> data sets needs to be one of equality.\n<p>If more than return more than one <em>source <\/em>rows is matched then SQL Server will throw the following error: <\/p>\n<p><em>Msg 8672, Level 16, State 1, Line 110 <\/em><br \/>\n<em>The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE\/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.<\/em><\/li>\n<li>There are limitations to the clauses you can include. For example, when there are two different <code>NOT MATCHED BY SOURCE<\/code> conditions. When there are two <code>NOT MATCHED BY SOURCE<\/code> conditions the first one needs to have an <code>AND<\/code> condition, and the condition can only reference<em> target<\/em> columns. The <code>second NOT MATCHED BY SOURCE<\/code> condition is only applied if the first <code>NOT MATCHED BY SOURCE<\/code> is not met. Also one of the <code>NOT MATCHED BY<\/code> conditions needs to perform a <code>DELETE<\/code> action and the other an <code>UPDATE<\/code> action.<\/li>\n<\/ul>\n<p>For a complete list of requirements and other considerations when using the <code>MERGE<\/code> statement please refer to the Microsoft Documentation referenced in <strong>The Basic MERGE Statement<\/strong> section above.<\/p>\n<h1>Cleanup<\/h1>\n<p>If you ran the example code in this article it created the <code>MergeDB<\/code> database on your test instance of SQL Server. The script in Listing 9 can be used to remove this database from your test instance<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE TempDB\r\n\r\nDROP DATABASE MergeDB;\r\nGO<\/pre>\n<p><strong>Listing 9: Removing the MergeDB database<\/strong><\/p>\n<h1>Manage Data Over Time Using the Basic MERGE Statement<\/h1>\n<p>If you are using SQL Server 2008 or above the <code>MERGE<\/code> statement is supported. A single <code>MERGE<\/code> statement can be used to replace individual <code>INSERT<\/code>, <code>UPDATE<\/code>, and\/or <code>DELETE<\/code> statements. The <code>MERGE<\/code> statement is ideal for helping manage source and target tables for complex extract, transform, and load situations. Next time an <code>INSERT<\/code>, <code>UPDATE<\/code> and <code>DELETE<\/code> statements are required to maintain the data in a <code>target<\/code> table, consider using the <code>MERGE<\/code> statement, to support these requirements.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Once data is in a table it needs to be maintained. New records will need to be added, and existing records will need to be updated, and\/or deleted. To keep data maintained, the insert, update or delete actions may need to be performed. This article will show how those three data manipulation language (DML) actions can be synchronized together within a single basic MERGE statement.&hellip;<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525],"tags":[],"coauthors":[11330],"class_list":["post-95308","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95308","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95308"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95308\/revisions"}],"predecessor-version":[{"id":95317,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95308\/revisions\/95317"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95308"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95308"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95308"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95308"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}