CODEWITHSUNDEEP
sql-servert-sqlmergefilter
Gideon
Gideon

Reputation: 18501

SQL: How to limit the number of records the MERGE statement will insert

Some sample data:

DECLARE @TARGET TABLE ( ID INT, value INT ) ;
DECLARE @SOURCE TABLE ( ID INT, value INT )

INSERT INTO @TARGET VALUES ( 1, 213 )             
INSERT INTO @TARGET VALUES ( 2, 3 )             
INSERT INTO @TARGET VALUES ( 3, 310 )             
INSERT INTO @TARGET VALUES ( 4, 43 )                     

INSERT INTO @SOURCE VALUES ( 1, 134 )             
INSERT INTO @SOURCE VALUES ( 2, 34 )             
INSERT INTO @SOURCE VALUES ( 13, 310 )             
INSERT INTO @SOURCE VALUES ( 14, 43 )             
INSERT INTO @SOURCE VALUES ( 15,32 )             
INSERT INTO @SOURCE VALUES ( 16, 30 )             
INSERT INTO @SOURCE VALUES ( 17, 60 )             
INSERT INTO @SOURCE VALUES ( 18, 5 )                   


MERGE @TARGET t USING (SELECT * FROM @SOURCE) AS s ON (t.id = s.id)
WHEN NOT MATCHED THEN
INSERT VALUES (s.id,s.value);

SELECT * FROM @TARGET

So I'm having a target table , and a source table. What I want to accomplish is that when there is a large number of not matched items, to only insert the x top items with the highest value.
Using top on the merge itself won't work, because that would limit the whole source table, I want to do something like 

WHEN NOT MATCHED 
LIMIT(5) AND ORDER BY Value DESC --only insert the 5 non-matches with the highest value
INSERT VALUES (s.id,s.value)

---- UPDATE ----
My MERGE statement also contains an WHEN MATCHED THEN statement: 

WHEN MATCHED THEN
UPDATE SET t.value = s.value

this sadly negates the answers given by Ian and Dog...

Upvotes: 1

Views: 8753

Answers (2)

Dog Ears
Dog Ears

Reputation: 10025

Isn't SET ROWCOUNT Deprecated, you could use the top clause if you do it like this:

;MERGE TOP (5) @TARGET t USING 
(SELECT TOP (100) PERCENT * FROM @SOURCE ORDER BY VALUE DESC) AS s ON (t.id = s.id)
WHEN NOT MATCHED 
THEN
INSERT VALUES (s.id,s.value);

SELECT * FROM @TARGET

The ORDER BY int the merge wont work unless you have a TOP Clause so using TOP (100) PERCENT tricks SQL into allowing the ordering.

Edit:

What about doing it in two steps?

;MERGE TOP (5) @TARGET t USING 
(SELECT TOP (100) PERCENT * FROM @SOURCE ORDER BY VALUE DESC) AS s ON (t.id = s.id)
WHEN NOT MATCHED 
THEN
INSERT VALUES (s.id,s.value);/*
WHEN MATCHED THEN
UPDATE SET t.value = s.value;*/

update t 
set t.Value = s.Value
from     @Target t
    join @Source s on t.ID = s.ID 
where t.Value <> s.Value

SELECT * FROM @TARGET

Upvotes: 3

Dead account
Dead account

Reputation: 19960

You can use SET ROWCOUNT n;

For example;

SET ROWCOUNT 4;
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE Quantity < 300;

See; http://msdn.microsoft.com/en-us/library/ms188774.aspx

Or you can do

Insert to @Target 
Select top 5 s.id, s.value from @Source s 
order by s.value desc ... etc.

Upvotes: 2

Related Questions