0

I'm trying to track the updated rows in my database by saving them in another table. I have a code like this:

"UPDATE Table1 SET [HastaSN] = @HastaSN WHERE Id = @Id; INSERT INTO Changes ([TableName],[Ids]) VALUES ('Table1 ', (SELECT IDENT_CURRENT('[Table1 ]')))"

I always get a table when I try to update a few rows in my database :

  TableName  Ids
    Table1  56697
    Table1  56697
    Table1  56697

It's just showing me the last ID of the table. Not every ID number. How do I fix this problem?

ismailakarim
  • 1
  • 5
  • 1
    Possible duplicate of [How do I use an INSERT statement's OUTPUT clause to get the identity value?](https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value) – Rikalous Jan 17 '19 at 13:13
  • It is not related to this insert. This is about the update. And the OUTPUT.UPDATED clause in mssql does not work, i think. – ismailakarim Jan 17 '19 at 13:16
  • 1
    Don't you already know the ID from the variable `@ID` which you use in your `WHERE`? The value of an `IDENTITY` doesnt change when. You `UPDATE` the row. – Thom A Jan 17 '19 at 13:17
  • I want to save IDs to another table automatically. Thus, other computers can monitor data changes. – ismailakarim Jan 17 '19 at 13:21
  • @ismailakarim if you check the docs you'll see that [OUTPUT](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017) works in INSERT, UPDATE, DELETE, and MERGE – Panagiotis Kanavos Jan 17 '19 at 13:21
  • If you want to save IDs "automatically", you may be looking for an `UPDATE` trigger. That will have the affected rows in the `deleted` and `inserted` pseudo-tables. – Jeroen Mostert Jan 17 '19 at 13:22
  • Ok. I ll look again. Thank you all. – ismailakarim Jan 17 '19 at 13:22
  • @ismailakarim if you *already* know that ID why don't you use it? In any case, the duplicate and the OUTPUT doc page shows how to write original and changed values to another table. If you want that to happen without modifying the INSERT statements use a trigger or the [system versioned tables](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017) introduced in SQL Server 2016 – Panagiotis Kanavos Jan 17 '19 at 13:23
  • How can i do that UPDATE trigger? Like OUTPUT.INSERTED? – ismailakarim Jan 17 '19 at 13:23
  • @ismailakarim that's described in the docs. Few people can remember every statement's syntax, we all open the docs. Googling for `t-sql create trigger` returns the doc page [CREATE TRIGGER](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017) at the top – Panagiotis Kanavos Jan 17 '19 at 13:23
  • Ok. I understood a bit. I have to examine more. – ismailakarim Jan 17 '19 at 13:25

1 Answers1

1

The OUTPUT clause should work for you. Here's an example of using it.

DROP TABLE IF EXISTS #t;
CREATE TABLE #t
(
    ID INT NOT NULL IDENTITY(1,1),
    SomeText VARCHAR(100)
);

DROP TABLE IF EXISTS #IDTable;
CREATE TABLE #IDTable (ID INT);

INSERT INTO #t (SomeText)
VALUES ('Hello'),
       ('World');

UPDATE #t
SET SomeText = 'World!'
OUTPUT Inserted.ID
INTO #IDTable
WHERE ID = 2;

SELECT ID FROM #IDTable;

In your example it might look something like this:

UPDATE Table1
SET [HastaSN] = @HastaSN
OUTPUT 'Table1', Inserted.Id
INTO Changes
WHERE Id = @Id
squillman
  • 13,363
  • 3
  • 41
  • 60