0
CREATE TABLE SourceProducts(
 ProductID INT,
 ProductName VARCHAR(50),
 Price DECIMAL(9,2)
)
GO
 
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(1,'Table',100)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(2,'Desk',80)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(3,'Chair',50)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(4,'Computer',300)
INSERT INTO SourceProducts(ProductID,ProductName, Price) VALUES(7,'Monitor',400)
GO
 
CREATE TABLE TargetProducts(
 ProductID INT,
 ProductName VARCHAR(50),
 Price DECIMAL(9,2)
)
GO
 
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(1,'Table',100)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(2,'Desk',180)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(5,'Bed',50)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(6,'Cupboard',300)
INSERT INTO TargetProducts(ProductID,ProductName, Price) VALUES(7,'Monitor',900)
GO

Currently I am using the above data as a sample to understand how merge functionality operates.

I am using the below code to upsert , but I have a condition that it should update only monitor not desk (I may replace this with other condition like todays date)

MERGE TargetProducts AS Target
 USING SourceProducts AS Source
 ON Source.ProductID = Target.ProductID
 -- For Updates
 WHEN MATCHED 
 THEN UPDATE SET
 Target.ProductName = Source.ProductName,
 Target.Price = Source.Price
 
 -- For Inserts
 WHEN NOT MATCHED BY Target THEN
 INSERT (ProductID,ProductName, Price) 
 VALUES (Source.ProductID,Source.ProductName, Source.Price);
 
 MERGE INTO TargetProducts AS target
 USING SourceProducts AS source
 ON target.ProductID = source.ProductID
 WHEN MATCHED 
 AND source.ProductName = 'Monitor' 
 THEN
 -- Update existing rows
 UPDATE SET 
 Target.ProductName = Source.ProductName,
 Target.Price = Source.Price
 WHEN NOT MATCHED 
 THEN
 -- Insert new rows
 INSERT (ProductID,ProductName, Price) 
 VALUES (Source.ProductID,Source.ProductName, Source.Price);

However , the condition isn't making any difference in this case. Where I am going wrong.

asked May 27, 2024 at 7:11
1
  • 2
    what do you want it to do if the productname is not 'monitor' ? Insert another record or do nothing Commented May 27, 2024 at 9:46

1 Answer 1

-2

your code seems correct, if you comment these: enter image description here

answered May 27, 2024 at 11:45
0

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.