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.
-
2what do you want it to do if the productname is not 'monitor' ? Insert another record or do nothingStephen Morris - Mo64– Stephen Morris - Mo642024年05月27日 09:46:46 +00:00Commented May 27, 2024 at 9:46
1 Answer 1
your code seems correct, if you comment these: enter image description here
answered May 27, 2024 at 11:45
lang-sql