none
Alternative to merge

    Question

  • I’ve table with data as below:

    RowId   Employeed Id     Level     

    1             10                          1                           

    2             10                          2            

    3             10                          3

    The level of employee can be changed, this change calculations are in temp table. For the above employee 10, data changed to:

    #temp_employee

    Employee Id       Level

    10                          2

    10                          4

    Is there a way to compare real employee table with temp #temp_employee table and update data if changed else delete. After comparison, real table should have data in temp table and rows 1 and 3 should be deleted and new row 10 – 4 should be inserted.

    Is it possible to not to use Merge?

    TIA

    Wednesday, May 15, 2019 8:09 PM

All replies

  • MERGE employee AS t
    USING (
    	SELECT EmployeeId, Level
    	FROM #temp_employee
    ) AS s ON t.EmployeeId = s.EmployeeId AND t.Level = s.Level
    WHEN NOT MATCHED BY t THEN
    	INSERT (
    		EmployeeId, 
    		Level
    	)
    	VALUES (
    		SOURCE.EmployeeId, 
    		SOURCE.Level
    	)
    WHEN NOT MATCHED BY s THEN
    	DELETE;


    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 15, 2019 8:29 PM
  • Hi bluepink, 

    If you don't want to use merge, you also could create new table to replace old one 

    create   table realtable1 (
     EmployeedId int ,   Level int   ) 
     insert into realtable1 
     select *  from (
    select  a.EmployeedId  ,   a.Level  from realtable a
    where exists (select 1  from #temp b where a.level=b.Level)
    union
    select   b.EmployeedId  ,   b.Level  from #temp b
    where not exists (select 1  from realtable a where a.level=b.Level)) t
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 16, 2019 5:26 AM
  • Hi bluepink

    CREATE TABLE realtable
    (id          INT IDENTITY(1, 1), 
     EmployeedId INT, 
     Level       INT
    ); 
    INSERT INTO realtable
    VALUES
    (10, 
     1
    ),
    (10, 
     2
    ),
    (10, 
     3
    );
    CREATE TABLE #temp_employee
    (EmployeeId INT, 
     Level      INT
    );
    INSERT INTO #temp_employee
    VALUES
    (10, 
     2
    ),
    (10, 
     4
    );
    
     insert into realtable 
    select * from  #temp_employee tmp where not exists(select 1 from realtable  where tmp.level=level)
    delete  from  realtable  where not exists(select 1 from #temp_employee  where realtable.level=level)
    
    select * from realtable
    
    --result
    
    --id	EmployeedId	Level
    --4	   10	      4
    --2	   10	      2

    Hope it can help you.

     

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 


    Thursday, May 16, 2019 6:16 AM