CODEWITHSUNDEEP
sql-server
Isky
Isky

Reputation: 29

Query for updating other table while inserting data into one table

I am looking for solution to update and insert Table2 while inserting data into Table1,

Table1 

---------------------------------------
| id |Student name | % | Date of Exam |
---------------------------------------
| 1  | Tom         | 80 | 12/03/2015  |
| 2  | Jack        | 90 | 12/03/2015  |
| 1  | Tom         | 85 | 21/05/2015  |
---------------------------------------

Table2 (to be updated) 

---------------
| id  |  %    |
---------------
|  1  |  85   |
|  2  |  90   |
---------------

If the "id" is not present in the Table2 I want to insert, and if it exists, the table's row should be updated.

Please someone help me to find solution.

Thanks in advance.

Upvotes: 1

Views: 313

Answers (2)

StuartLC
StuartLC

Reputation: 107237

If you need a persisted table solution, you could use an AFTER trigger to recalculate the last result every time a row is inserted int Table1 and insert / update Table2 ("LastScore") accordingly, using the inserted pseudo rows. 

CREATE TRIGGER tCalcLastScore ON Table1
AFTER INSERT, UPDATE AS
BEGIN
  DELETE a
  FROM LastScore a
  INNER JOIN inserted i
  on i.id = a.id;

  INSERT INTO LastScore(id, [Percent])
    SELECT id, [Percent] FROM
    ( SELECT t1.id, t1.[Percent], 
      ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t1.DateOfExam DESC) AS RowId
      FROM Table1 t1 INNER JOIN inserted i 
           ON i.id = t1.id
    ) x WHERE RowId = 1;
END;

SqlFiddle here

Notes

  • Remember that the trigger must handle multiple inserted rows - I've used a ROW_NUMBER function with a filter to determine the last row for each student.
  • If rows can be deleted from the first table, you'll also need to add a trigger to ON DELETED and handle the deleted pseudo rows as well
  • I've been lazy and just deleted + reinserted into the second table (LastScore) but a merge is also possible.

However, a non-persisted alternative is to compute the last score each time on the fly using a view:

CREATE VIEW LastScore AS
   SELECT id, [Percent] 
   FROM
   (
     SELECT id, [Percent],
          ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t1.DateOfExam DESC) AS RowId
          FROM Table1 t1
   ) x 
   WHERE RowId = 1;

View Fiddle

The performance of the view should be reasonable provided that there is an index on Table1(id, DateOfExam)

Upvotes: 1

Sarvesh Mishra
Sarvesh Mishra

Reputation: 2072

You can use Trigger (on insert) or a stored procedure to insert or update one or more tables.

If table1 is ever updated, table2 should also be updated accordingly. Writing a Trigger on insert and update would be useful.

Upvotes: 0

Related Questions