CODEWITHSUNDEEP
sql-servert-sqlmergesql-server-2012ssms
Mar1009
Mar1009

Reputation: 811

How to merge data from one table to another table in SQL Server

I have table like this :

Table A:

customerId     mobNo1    mobNo2    mobNo3
-----------------------------------------
123            999       997       987
124            888       666       555

Table B:

customerId     mobNumbers    
-------------------------
123            999       
123            997       
123            987       
124            888       
124            666       
124            555

I need to write a MERGE statement so that the data from tableA to get updated or inserted in to tableB

This is what I tried:

MERGE tableB tgt
USING (SELECT * FROM tableA) src ON src.customerId = tgt.customerId

WHEN MATCHED THEN
    UPDATE 
        SET mobNumbers = src.mobNo1,
            mobNumbers = src.mobNo2,
            mobNumbers = src.mobNo3,
 
WHEN NOT MATCHED THEN 
    INSERT (customerID, mobileNumbers)
    VALUES (src.customerID, src.mobNo1),
           (src.customerID, src.mobNo2), 
           (src.customerID, src.mobNo3);
END

But this is not working - any idea how to achieve this?

Upvotes: 1

Views: 6284

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

You can't say SET col = something, col = something else whether you use MERGE or not - each SET directive applies to exactly one row, so mentioning the same column multiple times and expecting that to affect multiple rows differently is just not how it works.

The simplest approach IMHO doesn't involve MERGE at all (and not just because of my long-standing bias against it or that several people agree); just delete the customers that match and then insert the new set for each customer. This doesn't have error handling, but is the cleanest path IMHO:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

-- just wipe out all existing rows for matching customers
DELETE b FROM dbo.tableB AS b
  INNER JOIN dbo.tableA AS a
  ON a.customerId = b.customerId;

-- re-populate the whole set for each matching customer
INSERT dbo.tableB(customerId, mobNumbers)
  SELECT customerId, mobNumbers
  FROM dbo.tableA AS a
  UNPIVOT (mobNumbers FOR mobs IN ([mobNo1],[mobNo2],[mobNo3])) AS u
  WHERE NOT EXISTS (SELECT 1 FROM dbo.tableB WHERE customerId = a.customerId);

COMMIT TRANSACTION;

Upvotes: 5

Erick de Vathaire
Erick de Vathaire

Reputation: 173

You can do without those UNION using UNPIVOT

Create tables and insert data

CREATE TABLE #TableA (
    customerId INT
    , mobNo1 INT
    , mobNo2 INT
    , mobNo3 INT )
CREATE TABLE #TableB (
    customerId INT
    , mobNumbers INT )
INSERT #TableA VALUES 
      (123, 999, 997, 988)
    , (124, 888, 666, 555);
INSERT #TableB VALUES
      (123, 999)
    , (123, 997)
    , (123, 987)
    , (124, 888)
    , (124, 666)
    , (124, 555);

The unpivot will "join" the columns that are descripted, you can see the command alone:

SELECT * FROM #TableA
UNPIVOT (
    Value
    FOR Origin
        IN (mobNo1, mobNo2, mobNo3)) Unp

If you do it like this, it's easier to put new columns in the query, only write the name of the column in the unpivot

Merge:

MERGE #TableB B
USING (
    SELECT * FROM #TableA
    UNPIVOT (
        Value
        FOR Origin
            IN (mobNo1, mobNo2, mobNo3)) Unp
    ) A
    ON
        B.customerId = B.customerId
        AND B.mobNumbers = A.Value
    WHEN NOT MATCHED THEN
        INSERT VALUES (customerId, Value)
    --WHEN NOT MATCHED BY SOURCE THEN   --This option only if you want to delete the numbers that aren't in #TableA
    --  DELETE
    ;

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

The root of the problem is that one source row maps into 3 target rows, so you need to "massage" the source appropriately.

I haven't actually tested it, but something like this should put you on the right track:

MERGE tableB tgt
USING
    (
        SELECT customerId, mobNo1 mobNo FROM tableA
        UNION
        SELECT customerId, mobNo2 FROM tableA
        UNION
        SELECT customerId, mobNo3 FROM tableA
    ) src
ON
    src.customerId = tgt.customerId
    AND src.mobNumbers = tgt.mobNo

WHEN NOT MATCHED THEN 
    INSERT (customerID, mobileNumbers)
    VALUES (src.customerID, src.mobNo);

By the way, MERGE cannot modify the same row more than once (you'll get a run-time error if you try).

Upvotes: 2

Related Questions