MERGE example

1. Update an existing product's UnitsInStock if it exists, otherwise insert a new product.

SQL Server Query 1

            
 -- Update an existing product's UnitsInStock if it exists, otherwise insert a new product.  

 -- Create a temporary  table to show MERGE  

 SELECT 1 AS ProductID, CAST('the product name' AS NVARCHAR(MAX)) AS ProductName,   

 	CAST(0.00 AS money) AS UnitPrice, CAST(0 AS INT) AS UnitsInStock, CAST(0 AS bit) AS Discontinued  

 INTO #PRODUCTTEMP  

 -- Clear the temporary table  

 DELETE FROM #PRODUCTTEMP WHERE ProductID = 1  

 -- Inserts some value in temporary table  

 INSERT INTO #PRODUCTTEMP VALUES (1, 'Chai', 18.00, 100, 0),  

 	(2, 'Chang', 19.00, 17, 0),  

 	(3, 'Aniseed Syrup', 10.00, 13, 0),  

 	(4, 'Ikura', 31.00, 32, 0),  

 	(5, 'Pavlova', 17.45, 29, 0)  

   

 MERGE INTO #PRODUCTTEMP AS Target   

 USING (VALUES  

 	(78, 'New Gadget', 120.35, 50, 0), -- New product (ProductID, ProductName, UnitPrice, UnitsInStock, Discontinued)  

 	(1, 'Chai', 18.00, 10000, 0) -- Existing product (Chai - ProductID 1)  

 ) AS Source (ProductID, ProductName, UnitPrice, UnitsInStock, Discontinued)  

 ON Target.ProductID = Source.ProductID  

 WHEN MATCHED THEN  

 	UPDATE SET  

         Target.UnitsInStock = Target.UnitsInStock + Source.UnitsInStock, -- Add to existing stock  

         Target.UnitPrice = Source.UnitPrice -- Update UnitPrice if changed  

 WHEN NOT MATCHED THEN  

 	INSERT (ProductID, ProductName, UnitPrice, UnitsInStock, Discontinued)  

     VALUES (Source.ProductID, Source.ProductName, Source.UnitPrice, Source.UnitsInStock, Source.Discontinued);  

   

 SELECT * FROM #PRODUCTTEMP  

   

 DROP TABLE #PRODUCTTEMP  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("ProductName", "MetricType", "MetricValue")  

     .From(new SqlQueryBuilder().Select()  

         .Columns("ProductID","ProductName","UnitsInStock","UnitsOnOrder")  

         .From("Products")  

         .Where(new Where(new Column("Discontinued").Equale(0))  

             .AND(new Column("UnitsInStock").GreaterThan(0))  

             .AND(new Column("ProductID").LessThan(30))), "SourceTable"  

     )  

     .UnPivot(new UnPivot(new Column("MetricValue"))  

         .For(new Column("MetricType")).IN("UnitsInStock", "UnitsOnOrder")  

         .TableName(new Table("UnpivotTable"))  

     )  

     .OrderBy(new OrderBy().SetColumnAscending("ProductName")  

         .SetColumnAscending("MetricType"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT CAST (1 AS INT) AS ProductID,
       CAST (@pMAIN_2609201712155521820 AS NVARCHAR (MAX)) AS ProductName,
       CAST (0.00 AS MONEY) AS UnitPrice,
       CAST (0 AS INT) AS UnitsInStock,
       CAST (0 AS BIT) AS Discontinued
INTO #PRODUCTTEMP;

DELETE #PRODUCTTEMP
WHERE ProductID = @pMAIN_2609201712155521821;

INSERT INTO #PRODUCTTEMP
VALUES (@pMAIN_2609201712155521822, @pMAIN_2609201712155521823, @pMAIN_2609201712155521824, @pMAIN_2609201712155521825, @pMAIN_2609201712155521826),
(@pMAIN_2609201712155521827, @pMAIN_2609201712155521828, @pMAIN_2609201712155521829, @pMAIN_260920171215552182_10, @pMAIN_260920171215552182_11),
(@pMAIN_260920171215552182_12, @pMAIN_260920171215552182_13, @pMAIN_260920171215552182_14, @pMAIN_260920171215552182_15, @pMAIN_260920171215552182_16),
(@pMAIN_260920171215552182_17, @pMAIN_260920171215552182_18, @pMAIN_260920171215552182_19, @pMAIN_260920171215552182_20, @pMAIN_260920171215552182_21),
(@pMAIN_260920171215552182_22, @pMAIN_260920171215552182_23, @pMAIN_260920171215552182_24, @pMAIN_260920171215552182_25, @pMAIN_260920171215552182_26);

MERGE INTO #PRODUCTTEMP
 AS Target
USING (VALUES (@pMAIN_260920171215552182_27, @pMAIN_260920171215552182_28, @pMAIN_260920171215552182_29, @pMAIN_260920171215552182_30, @pMAIN_260920171215552182_31), (@pMAIN_260920171215552182_32, @pMAIN_260920171215552182_33, @pMAIN_260920171215552182_34, @pMAIN_260920171215552182_35, @pMAIN_260920171215552182_36)) AS Source(ProductID, ProductName, UnitPrice, UnitsInStock, Discontinued) ON Target.ProductID = Source.ProductID
WHEN MATCHED THEN UPDATE 
SET Target.UnitsInStock = Target.UnitsInStock + Source.UnitsInStock,
    Target.UnitPrice    = Source.UnitPrice
WHEN NOT MATCHED THEN INSERT (ProductID, ProductName, UnitPrice, UnitsInStock, Discontinued) VALUES (Source.ProductID, Source.ProductName, Source.UnitPrice, Source.UnitsInStock, Source.Discontinued);

SELECT *
FROM #PRODUCTTEMP;

DROP TABLE #PRODUCTTEMP;


            
        

Parameters (If used)

Query Results 1:

2. Update an existing product's UnitsInStock if it exists, otherwise insert a new product with table hints and insert without column names

SQL Server Query 2

            
 -- Update an existing product's UnitsInStock if it exists, otherwise insert a new product.  

 -- Create a temporary  table to show MERGE  

 SELECT 1 AS ProductID, CAST('the product name' AS NVARCHAR(MAX)) AS ProductName,   

 	CAST(0.00 AS money) AS UnitPrice, CAST(0 AS INT) AS UnitsInStock, CAST(0 AS bit) AS Discontinued  

 INTO #PRODUCTTEMP  

 -- Clear the temporary table  

 DELETE FROM #PRODUCTTEMP WHERE ProductID = 1  

 -- Inserts some value in temporary table  

 INSERT INTO #PRODUCTTEMP VALUES (1, 'Chai', 18.00, 100, 0),  

 	(2, 'Chang', 19.00, 17, 0),  

 	(3, 'Aniseed Syrup', 10.00, 13, 0),  

 	(4, 'Ikura', 31.00, 32, 0),  

 	(5, 'Pavlova', 17.45, 29, 0)  

   

 MERGE INTO #PRODUCTTEMP WITH(ROWLOCK) AS Target   

 USING (VALUES  

 	(78, 'New Gadget', 120.35, 50, 0), -- New product (ProductID, ProductName, UnitPrice, UnitsInStock, Discontinued)  

 	(1, 'Chai', 18.00, 10000, 0) -- Existing product (Chai - ProductID 1)  

 ) AS Source (ProductID, ProductName, UnitPrice, UnitsInStock, Discontinued)  

 ON Target.ProductID = Source.ProductID  

 WHEN MATCHED THEN  

 	UPDATE SET  

         Target.UnitsInStock = Target.UnitsInStock + Source.UnitsInStock, -- Add to existing stock  

         Target.UnitPrice = Source.UnitPrice -- Update UnitPrice if changed  

 WHEN NOT MATCHED THEN  

 	INSERT   

     VALUES (Source.ProductID, Source.ProductName, Source.UnitPrice, Source.UnitsInStock, Source.Discontinued);  

   

 SELECT * FROM #PRODUCTTEMP  

   

 DROP TABLE #PRODUCTTEMP  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column(new CAST(new Column("1"), SqlDataType.INT), "ProductID")  

     .Column(new CAST(new Column().WithValue("the product name"), SqlDataType.NVARCHAR, "MAX"), "ProductName")  

     .Column(new CAST(new Column("0.00"), SqlDataType.MONEY), "UnitPrice")  

     .Column(new CAST(new Column("0"), SqlDataType.INT), "UnitsInStock")  

     .Column(new CAST(new Column("0"), SqlDataType.BIT), "Discontinued")  

     .INTO(new Table("#PRODUCTTEMP"))  

     .DeleteFrom("#PRODUCTTEMP").Where(new Where(new Column("ProductID").Equale(1)))  

     .InsertInto("#PRODUCTTEMP", new List<List<object>>() {  

         new List<object>() {1, "Chai", 18.00, 100, 0 },  

         new List<object>() {2, "Chang", 19.00, 17, 0},  

         new List<object>() {3, "Aniseed Syrup", 10.00, 13, 0},  

         new List<object>() {4, "Ikura", 31.00, 32, 0},  

         new List<object>() {5, "Pavlova", 17.45, 29, 0}  

     })  

     .Merge(new MERGE("#PRODUCTTEMP", new List<IHint>() { new NOLOCK() })  

         .AS("Target")  

         .USING_VALUES(new VALUES(new List<List<object>>()  

         {  

             new List<object>(){ 78, "New Gadget", 120.35, 50, 0 },  

             new List<object>(){ 1, "Chai", 18.00, 10000, 0 }  

         }, "Source", "ProductID", "ProductName", "UnitPrice", "UnitsInStock", "Discontinued"))  

         .ON(new Column("Target.ProductID").Equale(new Column("Source.ProductID")))  

         .WHEN_MATCHED_THEN(new SqlQueryBuilder()  

             .MergeUpdate(new List<ISetValue>()  

             {  

                 new SetValue("Target.UnitsInStock", new ColumnArithmatic("Target.UnitsInStock").ADD("Source.UnitsInStock")),  

                 new SetValue("Target.UnitPrice", new Column("Source.UnitPrice"))  

             })  

         )  

         .WHEN_NOT_MATCHED_THEN(new SqlQueryBuilder()  

             .MergeInsert(new List<IInsertValue>()  

             {  

                 new InsertValue("ProductID", new Column("Source.ProductID")),  

                 new InsertValue("ProductName", new Column("Source.ProductName")),  

                 new InsertValue("UnitPrice", new Column("Source.UnitPrice")),  

                 new InsertValue("UnitsInStock", new Column("Source.UnitsInStock")),  

                 new InsertValue("Discontinued", new Column("Source.Discontinued")),  

             }, true)  

         )  

     )  

     .Select().Star().From("#PRODUCTTEMP")  

     .DropTable(new Table("#PRODUCTTEMP"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT CAST (1 AS INT) AS ProductID,
       CAST (@pMAIN_2609201712156000910 AS NVARCHAR (MAX)) AS ProductName,
       CAST (0.00 AS MONEY) AS UnitPrice,
       CAST (0 AS INT) AS UnitsInStock,
       CAST (0 AS BIT) AS Discontinued
INTO #PRODUCTTEMP;

DELETE #PRODUCTTEMP
WHERE ProductID = @pMAIN_2609201712156000911;

INSERT INTO #PRODUCTTEMP
VALUES (@pMAIN_2609201712156000912, @pMAIN_2609201712156000913, @pMAIN_2609201712156000914, @pMAIN_2609201712156000915, @pMAIN_2609201712156000916),
(@pMAIN_2609201712156000917, @pMAIN_2609201712156000918, @pMAIN_2609201712156000919, @pMAIN_260920171215600091_10, @pMAIN_260920171215600091_11),
(@pMAIN_260920171215600091_12, @pMAIN_260920171215600091_13, @pMAIN_260920171215600091_14, @pMAIN_260920171215600091_15, @pMAIN_260920171215600091_16),
(@pMAIN_260920171215600091_17, @pMAIN_260920171215600091_18, @pMAIN_260920171215600091_19, @pMAIN_260920171215600091_20, @pMAIN_260920171215600091_21),
(@pMAIN_260920171215600091_22, @pMAIN_260920171215600091_23, @pMAIN_260920171215600091_24, @pMAIN_260920171215600091_25, @pMAIN_260920171215600091_26);

MERGE INTO #PRODUCTTEMP
 AS Target
USING (VALUES (@pMAIN_260920171215600091_27, @pMAIN_260920171215600091_28, @pMAIN_260920171215600091_29, @pMAIN_260920171215600091_30, @pMAIN_260920171215600091_31), (@pMAIN_260920171215600091_32, @pMAIN_260920171215600091_33, @pMAIN_260920171215600091_34, @pMAIN_260920171215600091_35, @pMAIN_260920171215600091_36)) AS Source(ProductID, ProductName, UnitPrice, UnitsInStock, Discontinued) ON Target.ProductID = Source.ProductID
WHEN MATCHED THEN UPDATE 
SET Target.UnitsInStock = Target.UnitsInStock + Source.UnitsInStock,
    Target.UnitPrice    = Source.UnitPrice
WHEN NOT MATCHED THEN INSERT VALUES (Source.ProductID, Source.ProductName, Source.UnitPrice, Source.UnitsInStock, Source.Discontinued);

SELECT *
FROM #PRODUCTTEMP;

DROP TABLE #PRODUCTTEMP;


            
        

Parameters (If used)

Query Results 2:

3. Merge with Products table (Transcation is rollbacked)

SQL Server Query 3

            
 BEGIN TRANSACTION  

 MERGE Products AS Target   

 USING (VALUES  

     (78, 'New Gadget', 1, 1, 100, 50, 0), -- New product (ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued)  

     (1, 'Chai', 1, 1, 18.00, 50, 0)   -- Existing product (Chai - ProductID 1)  

 ) AS Source (ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued)  

 ON Target.ProductID = Source.ProductID -- Match condition  

 WHEN MATCHED THEN  

     UPDATE SET  

         Target.UnitsInStock = Target.UnitsInStock + Source.UnitsInStock, -- Add to existing stock  

         Target.UnitPrice = Source.UnitPrice -- Update UnitPrice if changed;  

 WHEN NOT MATCHED THEN  

     INSERT (ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued)  

     VALUES (Source.ProductName, Source.SupplierID, Source.CategoryID, Source.UnitPrice, Source.UnitsInStock, Source.Discontinued);  

 SELECT TOP 5 * FROM Products  

 ROLLBACK TRANSACTION  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Merge(new MERGE("Products")  

         .AS("Target")  

         .USING_VALUES(new VALUES(new List<List<object>>()   

         {   

             new List<object>() { 78, "New Gadget", 1, 1, 100, 50, 0 },  

             new List<object>() { 1, "Chai", 1, 1, 18.00, 50, 0 }  

         }, "Source", "ProductID", "ProductName", "SupplierID", "CategoryID", "UnitPrice", "UnitsInStock", "Discontinued"))  

         .ON(new Column("Target.ProductID").Equale(new Column("Source.ProductID")))  

         .WHEN_MATCHED_THEN(new SqlQueryBuilder()  

             .MergeUpdate(new List<ISetValue>()   

             {   

                 new SetValue("Target.UnitsInStock", new ColumnArithmatic("Target.UnitsInStock").ADD("Source.UnitsInStock")),  

                 new SetValue("Target.UnitPrice", new Column("Source.UnitPrice"))  

             })  

         )  

         .WHEN_NOT_MATCHED_THEN(new SqlQueryBuilder()  

             .MergeInsert(new List<IInsertValue>()   

             {   

                 new InsertValue("ProductName", new Column("Source.ProductName")),  

                 new InsertValue("SupplierID", new Column("Source.SupplierID")),  

                 new InsertValue("CategoryID", new Column("Source.CategoryID")),  

                 new InsertValue("UnitPrice", new Column("Source.UnitPrice")),  

                 new InsertValue("UnitsInStock", new Column("Source.UnitsInStock")),  

                 new InsertValue("Discontinued", new Column("Source.Discontinued")),  

             })  

         )  

     )  

     .Select().Top(5).Star().From("Products")  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
MERGE INTO Products
 AS Target
USING (VALUES (@pMAIN_2609201712156211050, @pMAIN_2609201712156211051, @pMAIN_2609201712156211052, @pMAIN_2609201712156211053, @pMAIN_2609201712156211054, @pMAIN_2609201712156211055, @pMAIN_2609201712156211056), (@pMAIN_2609201712156211057, @pMAIN_2609201712156211058, @pMAIN_2609201712156211059, @pMAIN_260920171215621105_10, @pMAIN_260920171215621105_11, @pMAIN_260920171215621105_12, @pMAIN_260920171215621105_13)) AS Source(ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued) ON Target.ProductID = Source.ProductID
WHEN MATCHED THEN UPDATE 
SET Target.UnitsInStock = Target.UnitsInStock + Source.UnitsInStock,
    Target.UnitPrice    = Source.UnitPrice
WHEN NOT MATCHED THEN INSERT (ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued) VALUES (Source.ProductName, Source.SupplierID, Source.CategoryID, Source.UnitPrice, Source.UnitsInStock, Source.Discontinued);

SELECT TOP 5 *
FROM Products;


            
        

Parameters (If used)

Query Results 3: