StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POInsert Records that are not exists in Target Table and delete records that are not exists in source dataset Using MERGE
text
Body
copied!<p>I have a table With data like below (<code>Table 1</code>)</p> <pre><code>id valueId Value ----------- ----------- -------------------------------------------------- 1 1 Value 1 1 1 Value 2 1 1 Value 3 1 2 Value 1 1 2 Value 2 1 2 Value 3 </code></pre> <p>And i have another dataset like below (<code>DataSet</code>)</p> <pre><code>id valueId Value ----------- ----------- ------- 1 1 Value 1 1 1 Value 2 1 1 Value 4 </code></pre> <p>now i need to insert records that are not exists in the <code>Table 1</code> (You can see the record </p> <pre><code>id valueId Value ----------- ----------- ------- 1 1 Value 4 </code></pre> <p>is not in the <code>Table 1</code>, that record should insert) and need to delete records from <code>Table 1</code> that are not in <code>DataSet</code> which is </p> <pre><code>id valueId Value ----------- ----------- ------- 1 1 Value 3 </code></pre> <p>, but without affecting other records (other records means records that <code>id=1 and valueId=2</code>).</p> <p>I have used the following T-SQL that i have wrote it using <code>MERGE</code>, It inserts the missing records but it deletes all the records that are not in the <code>DataSet</code></p> <pre><code>DECLARE @tmp_value AS TABLE ( id INT , valueId INT , [Value] NVARCHAR(50) ) ; INSERT @tmp_value ( [id], [valueId], [Value] ) VALUES ( 1, 1, N'Value 1' ), ( 1, 1, N'Value 2' ), ( 1, 1, N'Value 3' ), ( 1, 2, N'Value 1' ), ( 1, 2, N'Value 2' ), ( 1, 2, N'Value 3' ) ; --SELECT * --FROM @tmp_value AS TV ; WITH cte AS ( SELECT 1 AS id , 1 AS valueId , 'Value 1' AS [Value] UNION SELECT 1 AS id , 1 AS valueId , 'Value 2' AS [Value] UNION SELECT 1 AS id , 1 AS valueId , 'Value 4' AS [Value] ) MERGE @tmp_value AS TV USING cte ON [cte].[id] = [TV].[id] AND [cte].[valueId] = [TV].[valueId] AND [cte].[Value] = [TV].[Value] WHEN NOT MATCHED THEN INSERT VALUES ( id , [valueId] , [Value] ) WHEN NOT MATCHED BY SOURCE THEN DELETE ; SELECT * FROM @tmp_value </code></pre> <p><strong>Expected Result:</strong></p> <pre><code>id valueId Value ----------- ----------- -------------------------------------------------- 1 1 Value 1 1 1 Value 2 1 1 Value 4 1 2 Value 1 1 2 Value 2 1 2 Value 3 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload