0

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?

gpilka
  • 11
  • 4
  • Sounds like a task ideally suited to a DML trigger...? – Stu Nov 01 '22 at 13:37
  • 1
    I second the notion of a trigger for this. But be careful using `@@identity`. You should instead use `SCOPE_IDENTITY`. And whatever you do make sure your trigger is set based an not using scalar variables. – Sean Lange Nov 01 '22 at 13:52
  • 1
    Identity would not be relevant, you would join on the `inserted` table – Stu Nov 01 '22 at 13:59
  • Why have a summary table, and not a summary `VIEW`? Then you don't *need* to `UPDATE` the other table every time. – Thom A Nov 01 '22 at 15:32
  • As far as i understand triggers, everytime there is an insert action, no matter which method is used, there will always be a check for the trigger, right? How does it affect the performance? @Larnu I recently started in a new company, and the database model is not something i'm responsible for. Maybe your assumption is right, but i don't know how it would affect the big picture. – gpilka Nov 01 '22 at 18:25

1 Answers1

0

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
Patrick Hurst
  • 2,086
  • 1
  • 3
  • 13
  • Now this actually looks like something i want to do. I will try it tomorrow. Thank you! – gpilka Nov 01 '22 at 18:17
  • Your idea with the Output Clause was very promising, but sadly there is one problem which i now recognize :/ The INSERT Statement which creates the SummaryTable entries does not provide the SingleTable entries id's. Instead of the SummaryTable automaticly creates new ID's for the Entries. So i guess there is no Chance to get the SingleTable ID's in the OUTPUT, right? – gpilka Nov 02 '22 at 11:11
  • The background is: The created SummaryTable entries get a summary_key. And the summary key has to be inserted in every single SingleTable entries which were summarized before. So to know which entries get which key i need to know their id's. – gpilka Nov 02 '22 at 11:28
  • OUTPUT does include that. It's part of the INSERTED table and can be referenced by it's name. The example provided above is actually doing this. The ID column is an IDENTITY, not provided by the insert statement, but part of the OUTPUT: `DECLARE @table TABLE (ID INT IDENTITY` `OUTPUT Inserted.ID, ` – Patrick Hurst Nov 02 '22 at 16:15