CODEWITHSUNDEEP
sqlsql-server
user3038399
user3038399

Reputation: 91

Compare data between two tables and update one table data

I have two tables and i need to compare data and update one table records. Please let me know how this can be done, i am trying to not use merge. This is the scenario

Proj1 Table This is the first table where data needs to be synchronized 

ID Text 
1   R1
2   R2
3   R3

Proj2 Table This is the table where data updates are taking place 

    ID Text Active
    3   R1    1
    4   R3    1
    5   R4    1

After a compare is done on Text field between both these tables result should be similar to below. We are syncing data in Proj2 to similar to Proj1.

    ID Text Active
    3   R1    1 (Ignore as it exists in both tables)
    4   R3    1 (Ignore as it exists in both tables)
    5   R4    0 (Update to inactive as it does not exist Proj1 table)
    6   R2    1 (Insert as it does not exist in Proj2 table)

Upvotes: 0

Views: 7983

Answers (4)

Antonio
Antonio

Reputation: 535

Another approach would be to use a cursor, if you really don't want to use MERGE

-declare a cursor on the first table and scroll the row, then for each row selct the corresponding row in the second table and ac accordingly, if found/not found...

not the best performance-related suggestion though...

Upvotes: 0

Zak
Zak

Reputation: 41

-- update records in table 2 that cannot be found in table 1

update P2
set P2.[Active] = 0
from [Proj2Table] as P2
left join [Proj1TAble] as P1 on P1.[text] = P2.[text]
where P1.[id] is null;

-- update records in table 2 that can be found in table 1
update P2
set P2.[Active] = 1
from [Proj2Table] as P2
join [Proj1TAble] as P1 on P1.[text] = P2.[text];

-- insert missing records from table 1 into table 2 with active bit set
insert into [Proj2Table] ([id] , [text] , [active] )
select [id] , [text] , [active] = 1
from [Proj1Table] as P1
where not exists (select 1 from [Proj2Table] as P2 where P2.[text] = P1.[text])
;

Not sure if [id] column should match or is a pk/identity/sequence

Upvotes: 2

Julien Blanchard
Julien Blanchard

Reputation: 825

If you really can't use MERGE, you can simply split it into an update and an insert query :

INSERT INTO @Proj2(Text, Active)
SELECT Text,1 FROM @Proj1 p1
WHERE NOT EXISTS(
    SELECT * 
    FROM @Proj2 p2
    WHERE p2.Text = p1.Text
);

UPDATE
    p2
SET
    p2.Active = CASE WHEN p1.id is null THEN 0 ELSE 1 END
FROM
    @Proj2 p2
LEFT JOIN
    @Proj1 p1
ON
    p2.Text = p1.Text;

This assumes that your ID is an auto-increment.

Thsi is pretty much like Zak's new answer, but with the 2 update queries merged.

Upvotes: 2

Zak
Zak

Reputation: 41

merge [Proj2Table] target
using ( select [id], [text] from [Proj1Table] ) source ([id], [text] )
on target.[id] = source.[id]
when not matched by source
then update
set target.[Active] = 0
when not matched by target
then insert
    ([id], [text] )
values( source.[id], source.[text] )

Upvotes: 2

Related Questions