How to merge sql query

Question

select tctp.AccountDID, tp.rdbslicecode as [ProductSlice], Sum(isnull(tctp.Quantity,0)) as [ProductCount], Max(CASE WHEN LevelCapability = 'RecruitEdgeRDB' THEN 'Named License' ELSE '' END ) AS [LicenseModel] into #ProductAudit from productdata.dbo.HHContractProduct tctp with(nolock) inner join ProductData..hhproduct tp with(nolock) on tp.productid=tctp.productid where tctp.StatusCode='Active' and tctp.LevelCapability in ('ResumeAccess','RDBSeat','RecruitEdgeRDB','NamedRDBAccess') and tctp.ActiveStartDT tctp.ActiveEndDT>=getdate() and tp.ProductID not in ('FM12RDB') and tp.RDBProductType 'ViewBased' and tctp.AccountDID='AJ26106LNFG47J6YXMD' Group by tctp.AccountDID, tp.rdbslicecode,tctp.LevelCapability this query retun the result as below AccDid productslice productcount licenseModel =================================================================== AJ26106LNFG47J6YXMD US 1 Named License AJ26106LNFG47J6YXMD US 1 but i want the result like below AccDid productslice productcount licenseModel =================================================================== AJ26106LNFG47J6YXMD US 2 Named License

Elavarasan R · Answer

Hi, check it out below example and links.. Example MERGE esqlProductTarget T USING esqlProductSource S ON (S.ProductID = T.ProductID) WHEN MATCHED THEN UPDATE SET T.Name = S.Name, T.ProductNumber = S.ProductNumber, T.Color = S.Color WHEN NOT MATCHED BY TARGET THEN INSERT (ProductID, Name, ProductNumber, Color) VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT S.ProductID, $action into @MergeLog; SELECT MergeAction, count(*) FROM @MergeLog GROUP BY MergeAction References: https://www.c-sharpcorner.com/blogs/merge-query-in-sql http://www.sqlservertutorial.net/sql-server-basics/sql-server-merge/ https://www.geeksforgeeks.org/sql-merge-statement/ Video Reference: https://www.youtube.com/watch?v=5dk33HN8BX8 https://www.youtube.com/watch?v=f-M04utRvTM Good luck.