How to merge sql query

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<=getdate() and

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

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.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Elavarasan R7y ago
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.
+1