CODEWITHSUNDEEP
sql-servert-sqlmerge
Chris Crawshaw
Chris Crawshaw

Reputation: 319

T-SQL Merge from multiple source records

I've seen several similar questions but haven't found one that answers my question.

I have a source table with many individual notes for a company

CompanyName, Notes3
Company1, "spoke with someone"
Company2, "Email no longer works"
Company1, "Moved address"

I have a destination table (vchCompanyName is unique here)

vchCompanyName, vchNotes
Company1, "started business in 2005"
Company2, null

I want to end up with 

vchCompanyName, vchNotes
Company1, "started business in 2005
           spoke with someone
           Moved address"
Company2, "Email no longer works"

I have tried this code

WITH CTE
AS (SELECT ROW_NUMBER() OVER (PARTITION BY CompanyName, Notes3 ORDER BY CompanyName) RowNum, *
    FROM CompanyContact
    )

    merge dCompany as target
    using CTE as source
    on target.vchcompanyname = source.companyname 
    when matched and len(source.notes3)>0 and source.RowNum = 1
    then
        update set target.vchnote = vchnote + CHAR(13) + source.Notes3

But get the error

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. Which is accurate.

I have also tried STRING_AGG but get an undefined UDF error.

How do I change my code to run iteratively?

--EDIT-- I had tried the following update code

    WITH CTE
AS (SELECT ROW_NUMBER() OVER (PARTITION BY CompanyName, Notes3 ORDER BY CompanyName) RowNum, *
    FROM CompanyContact
    )
    UPDATE dCompany SET vchNote = vchNote + 
    (select CHAR(13) + cc.Notes3 from CompanyContact cc 
    inner JOIN dCompany dc ON dc.vchCompanyName COLLATE database_default = LEFT(cc.CompanyName,50) COLLATE database_default
    inner join CTE on dc.vchCompanyName COLLATE database_default = LEFT(CTE.CompanyName,50) COLLATE database_default
     WHERE LEN(cc.Notes3)>0
     and RowNum = 1     
     );

But get the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 1

Views: 1140

Answers (1)

MEdwin
MEdwin

Reputation: 2960

@Chris Crawshaw, I will approach this by doing a 'union all' on the source and destination table to pick up all the notes for each company. Then using the STUFF function, it is easy to concatenate all the notes into one cell, while grouping by the induvidual company names. See the mockup below:

DECLARE @Source TABLE (CompanyName VARCHAR(20), Notes3 VARCHAR(50))
INSERT INTO @Source
SELECT 'Company1', 'spoke with someone' UNION ALL
SELECT 'Company2', 'Email no longer works' UNION ALL
SELECT 'Company1', 'Moved address'

DECLARE @Destination TABLE (vchCompanyName VARCHAR(20), vchNotes VARCHAR(500))
INSERT INTO @Destination
SELECT 'Company1', 'started business in 2005' UNION ALL
SELECT 'Company2', NULL




    ;WITH Temp AS (

    SELECT *
    FROM
    (
        SELECT *
        FROM
            @Destination D
        WHERE D.vchNotes is not null
        UNION ALL
        SELECT * 
        FROM
            @Source S  

    )h


    )

    update  D
    SET D.vchNotes=U.vchNotes

    FROM @Destination D
    LEFT JOIN(
    SELECT t2.vchCompanyName, vchNotes=STUFF((
        SELECT  ',' + vchNotes
        FROM Temp t1 where t1.vchCompanyName=t2.vchCompanyName
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    FROM
        @Destination t2
    GROUP BY
        t2.vchCompanyName
        )U ON
            U.vchCompanyName=D.vchCompanyName


    --TEST--
            SELECT * 
            FROM
            @Destination

Upvotes: 2

Related Questions