CODEWITHSUNDEEP
sql
Pat Legate
Pat Legate

Reputation: 51

Consolidating two tables in SQL using merge

I have a problem where I am trying to Merge and consolidate data from one table to another table.

The source and destination tables are identical except the destination needs to have a primary key on the first column.

The data input and desired results are follows: 

data in        Desired results   
 c1 c2 c3       c1   c2    c3  
+--+--+--+    +--+------+------+  
 1  A  x       1  A B C  x y  
 2  B  z       2  B C    z  
 3  A  z       3  A      z x y   
 1  A  y      +--+------+------+  
 3  A  y   
 1  B  x     
 2  C  z     
 1  C  x     
 3  A  x   
 1  A  x   
+--+--+--+

I initialize the tables... 

CREATE TABLE s (c1 char(2), c2 char(8), C3 char(8))

INSERT INTO s VALUES 
  ('1','A','x'), ('2','B','z'), ('3','A','z'), 
  ('1','A','y'), ('3','A','y'), ('1','B','x'), 
  ('2','C','z'), ('1','C','z'), ('3','A','x'), 
  ('1','A','x')

CREATE TABLE d (c1 char(2) PRIMARY KEY, c2 char(8), C3 char(8))

I can get it to work using Cursor...

DECLARE @c1 Char(2)
DECLARE @c2 char(8)
DECLARE @C3 char(8)

DECLARE cur CURSOR FOR SELECT c1, c2, C3 FROM s
OPEN cur
FETCH NEXT FROM cur INTO @c1, @c2, @C3
WHILE @@FETCH_STATUS = 0
BEGIN 
  UPDATE d SET 
    c2 = (CASE WHEN CHARINDEX(RTRIM(@c2), c2) > 0 THEN c2 ELSE SUBSTRING(RTRIM(c2) + ' ' + @c2, 1, 8) END),
    c3 = (CASE WHEN CHARINDEX(RTRIM(@c3), c3) > 0 THEN c3 ELSE SUBSTRING(RTRIM(c3) + ' ' + @c3, 1, 8) END)
    WHERE c1 = @c1
  IF @@ROWCOUNT = 0
  BEGIN
    INSERT INTO d (c1, c2, c3) 
      VALUES (@c1, @c2, @c3)
  END
  FETCH NEXT FROM cur INTO @c1, @c2, @c3
END
CLOSE cur
DEALLOCATE cur

But I cannot get Merge to work...
Error: Violation of PRIMARY KEY constraint 'PK__d__3213663B03BB8E22'. Cannot insert duplicate key in object 'dbo.d'. The duplicate key value is (1 ). 

MERGE INTO d USING s 
  ON (s.c1 = d.c1)
  WHEN MATCHED THEN 
    UPDATE SET 
      c2 = (CASE WHEN CHARINDEX(RTRIM(s.c2), d.c2) > 0 THEN d.c2 ELSE SUBSTRING(RTRIM(d.c2) + ' ' + s.c2, 1, 8) END),
      c3 = (CASE WHEN CHARINDEX(RTRIM(s.c3), d.c3) > 0 THEN d.c3 ELSE SUBSTRING(RTRIM(d.c3) + ' ' + s.c3, 1, 8) END)
  WHEN NOT MATCHED THEN
    INSERT (c1, c2, c3) VALUES (s.c1, s.c2, s.c3);

Is there a way to do this with a little more grace than going row by row?

Thanks for the help!

Upvotes: 4

Views: 54

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93734

You can avoid RBAR operation using For XML Path trick to concatenate the rows of each group. 

;WITH cte1 
     AS (SELECT DISTINCT c1,cs.c2 AS c2 
         FROM   s AS A 
                CROSS apply (SELECT DISTINCT c2 + ' ' 
                             FROM   s AS B 
                             WHERE  A.c1 = B.c1 
                             FOR xml path('')) cs (c2)), 
     cte3 
     AS (SELECT DISTINCT c1,cs.c3 AS c3 
         FROM   s AS A 
                CROSS apply (SELECT DISTINCT c3 + ' ' 
                             FROM   s AS B 
                             WHERE  A.c1 = B.c1 
                             FOR xml path('')) cs (c3)) 
SELECT A.c1, 
       a.c2, 
       b.c3 
FROM   cte1 A 
       INNER JOIN cte3 B 
               ON A.c1 = b.c1

Upvotes: 2

radar
radar

Reputation: 13425

This is best left to the presentation layer,

you can use corelated subquery to concatenate the values of c2, c3 based on the group by c1

you need to use STUFF along with for xml path to get the concatenated string

select c1, stuff( (select distinct ' ' + c2
               from s
               where s.c1 = outs.c1
               for xml path('')), 1,1,''),
           stuff( (select distinct ' ' + c3
               from s
               where s.c1 = outs.c1
               for xml path('')), 1,1,'')
from s outs
group by outs.c1

Upvotes: 0

Related Questions