locked
Merge statement inserting duplicate records in ONE BATCH RRS feed

  • Question

  • User325386820 posted

    I am using merge statement to insert and update the records into the table. But I am facing problem while updating records. Because I have duplicate records in my source table. Like this..

    CREATE TABLE #TempTable
    (
    	FirstName varchar(50),
    	LastName varchar(50),
    	Designation varchar(50),
    	PhoneNumber varchar(50),
    )
    CREATE TABLE #EmpTable
    (
    	FirstName varchar(50),
    	LastName varchar(50),
    	Designation varchar(50),
    	PhoneNumber varchar(50),
    )
    INSERT INTO #TempTable
    (
        FirstName,
        LastName,
        Designation,
        PhoneNumber
    )
    VALUES
    (
        'sehwag', -- FirstName - varchar
        'virender', -- LastName - varchar
        'Cricketer', -- Designation - varchar
        '111111' -- PhoneNumber - varchar
    ),
    (
        'sehwag', -- FirstName - varchar
        'virender', -- LastName - varchar
        'Actor', -- Designation - varchar
        '22222' -- PhoneNumber - varchar
    ),
    (
        'sehwag', -- FirstName - varchar
        'virender', -- LastName - varchar
        'SoftwareEngineer', -- Designation - varchar
        '3333333' -- PhoneNumber - varchar
    )

    I have used merge statement to insert records. This is my merge statement.

    MERGE INTO #EmpTable emp
    USING
    (
    	SELECT * FROM #TempTable
    ) temp 
    ON emp.firstname = temp.firstname
       AND emp.lastname = temp.lastname
    WHEN NOT MATCHED 
    THEN INSERT
    (
         firstname,
         lastname,
         Designation,
         PhoneNumber
    )
    VALUES
    (
         temp.firstname,
         temp.lastname,
         temp.Designation,
         temp.PhoneNumber
    )
    WHEN MATCHED THEN 
    UPDATE SET 
               Designation = temp.Designation,
               PhoneNumber  = temp.PhoneNumber;

    I got result like this. But it will not happen. Because it contains duplicate records. like this.

    I need to get result like this..I need to use merge statement only.

    Thanks..

    Saturday, July 30, 2016 3:06 PM

All replies

  • User-2057865890 posted

    Hi pammu141,

    You could select a specific record in the set of duplicate records to merge.

    MERGE INTO #EmpTable emp
    USING
    (
    	SELECT TOP 1 * FROM(
    		SELECT * FROM(
    			SELECT *,ROW_NUMBER() OVER (PARTITION BY firstname, lastname ORDER BY firstname DESC) AS Rn 
    			FROM #TempTable
    		) t
    	) p
    	ORDER BY Rn DESC
    ) temp 
    ON emp.firstname = temp.firstname
       AND emp.lastname = temp.lastname
    WHEN NOT MATCHED 
    THEN INSERT
    (
         firstname,
         lastname,
         Designation,
         PhoneNumber
    )
    VALUES
    (
         temp.firstname,
         temp.lastname,
         temp.Designation,
         temp.PhoneNumber
    );

    Best Regards,

    Chris

    Monday, August 1, 2016 8:53 AM
  • User325386820 posted
    Thanks for your reply...
    It will only work for table having multiple duplicate records...But my table contain both duplicate and unique records. At that time TOP 1 will not work.
    Monday, August 8, 2016 4:05 PM
  • User77042963 posted
    CREATE TABLE #TempTable
    (
    	FirstName varchar(50),
    	LastName varchar(50),
    	Designation varchar(50),
    	PhoneNumber varchar(50),
    )
    CREATE TABLE #EmpTable
    (
    	FirstName varchar(50),
    	LastName varchar(50),
    	Designation varchar(50),
    	PhoneNumber varchar(50),
    )
    INSERT INTO #TempTable
    (
        FirstName,
        LastName,
        Designation,
        PhoneNumber
    )
    VALUES
    (
        'sehwag', -- FirstName - varchar
        'virender', -- LastName - varchar
        'Cricketer', -- Designation - varchar
        '111111' -- PhoneNumber - varchar
    ),
    (
        'sehwag', -- FirstName - varchar
        'virender', -- LastName - varchar
        'Actor', -- Designation - varchar
        '22222' -- PhoneNumber - varchar
    ),
    (
        'sehwag', -- FirstName - varchar
        'virender', -- LastName - varchar
        'SoftwareEngineer', -- Designation - varchar
        '3333333' -- PhoneNumber - varchar
    )
     
     
    ;with mycte as (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY firstname, lastname ORDER BY 
    			Case when Designation ='Cricketer' then 3  
    			 when Designation ='Actor' then 2
    			 when Designation ='SoftwareEngineer' then 1
    			 End ) AS Rn 
    			FROM #TempTable
    )
    
    MERGE INTO #EmpTable emp
    USING (Select * from mycte where rn=1 ) temp ON 
    emp.firstname = temp.firstname
    AND emp.lastname = temp.lastname  
    WHEN NOT MATCHED 
    THEN INSERT
    (
         firstname,
         lastname,
         Designation,
         PhoneNumber
    )
    VALUES
    (
         temp.firstname,
         temp.lastname,
         temp.Designation,
         temp.PhoneNumber
    );
    
    Select * from #EmpTable
    
    drop table #EmpTable, #TempTable

    Monday, August 8, 2016 7:02 PM
  • User325386820 posted
    Hi limno...

    Thanks for your answere. It is just an example. My intention is if source table contains dupicate recrords. I want to avoid dupicate records using merge statements.

    In the above table contain three types of desigination (Cricketer,actor,software engineer). If table contain more than 100 types of designations. It is difficult to put conditions

    I need to avoid duplicate records while executing merge statements.

    Thanks...
    Tuesday, August 9, 2016 4:04 PM
  • User77042963 posted

    You need to come up with a role to define the order by clause. I use the case express hard coded to answer your sample.

    When you have over 100 types, which one will take the highest order (the last update )? You have to define it. If you don't care about it, you can sort it alphabetically  for your column desigination. 

    Tuesday, August 9, 2016 7:55 PM