How to UPDATE a row everytime after a new entry is added to a table through INSERT INTO?

Question

I have two tables: 'SingleTable' and 'SummaryTable' Then there is a function which summarizes some entries from SingleTable and writes it to SummaryTable.

Everytime a SummaryTable entry is inserted, there is also Summary Key as an attribute (not a pk, not unique). Now i want to write the created summary key in each SingleTable entry which was summarized.

Creating the SummaryTable entries (e.g.):

INSERT INTO SummaryTable
(some column names) 
SELECT DISTINCT
(some column names)
FROM SingleTable
WHERE
(some criteria)

I wrote something like this to get the latest created summary key:

UPDATE SingleTable
SET summarykey =
(SELECT summarykey 
FROM SummaryTable
WHERE id = @@IDENTITY
)
WHERE
(some criteria)

But as far as i unterstand i would have to run it everytime an summary was created and not after the INSERT INTO statement has finished.

Any idea how to do this?

Answer 1

This sounds like a case for the OUTPUT clause!

Here's an example:

DECLARE @table TABLE (ID INT IDENTITY, Txn_Date DATE, Count INT)
DECLARE @NewIDs TABLE (ID INT, Txn_Date DATE)
INSERT INTO @table (Txn_Date, Count)
OUTPUT Inserted.ID, Inserted.Txn_Date INTO @NewIDs

VALUES
('2020-01-01', 3),
('2020-01-04', 3),
('2020-01-05', 2),
('2020-01-10', 1),
('2020-01-18', 3),
('2020-01-20', 2),
('2020-01-24', 2),
('2020-01-28', 1)

SELECT *
  FROM @NewIDs

ID  Txn_Date
------------
1   2020-01-01
2   2020-01-04
3   2020-01-05
4   2020-01-10
5   2020-01-18
6   2020-01-20
7   2020-01-24
8   2020-01-28

Let's break that down. First we declare two table variables, @table is a place holder for some real table we want to insert into (your SummaryTable for example). We insert into it, but before we provide the values we define an OUTPUT clause. In this case we're only interested in the columns ID and Txn_Date. We can direct them to a table (or in this case another table variable) and then carry on with out insert.

Now we have the values we inserted in a table, which we can use for what ever else we might want to do. You could go on to update a table with an aggregate, for example:

UPDATE @someOtherTable
   SET aCol = NewRowCount
  FROM (
        SELECT COUNT(*) AS NewRowCount FROM @NewIDs
       ) a