CODEWITHSUNDEEP
sqlsql-serverdatabase-trigger
Teknas
Teknas

Reputation: 559

SQL Trigger - Check into other Table, If exists then Update else Insert

MSSQL

I have two tables. TableA and TableB

TableA 

+----+----+-----+
| ID |Name|Marks|
+----+----+-----+
| 1  |ABC |50   |
+----+----+-----+
| 2  |BCD |80   |
+----+----+-----+

TableB

+----+----+-----+
| ID |Name|Marks|
+----+----+-----+
| 1  |ABC |50   |
+----+----+-----+
| 4  |PQR |10   |
+----+----+-----+

On every insert into TableA I want to check if the same ID is there in TableB or not. If ID is not there in TableB then insert record into TableB, if ID found in TableB then update record in TableB.

How can i write trigger on TableA for these inserts.

Upvotes: 1

Views: 1582

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

This type of logic is a little tricky in SQL Server because it handles inserts as sets rather than as individual rows.

So, you might as well try the update and insert on all the values, using logic like this:

create trigger on tablea after insert
begin
    -- update everything that matches
    update b
        set marks = a.marks
        from tableb b join
             inserted a
             on b.id = a.id;

    -- insert what doesn't match
    insert into tableb
        select a.id, a.name, a.marks
        from inserted a
        where not exists (select 1 from tableb b where b.id = a.id);
end;

Upvotes: 3

Related Questions