How to get IDs of my batch update SQL Server

user_6856421 0
Viewed 959

How can I get the IDs of affected rows on my batch update? As I'm trying to insert on table tbl.history of all the update/transactions.

Below is my sample table:

table tbl.myTable
+------+-----------+------------+
|  ID  |   Amount  |    Date    |
+------+-----------+------------+
|  1   |    100    | 01/01/2019 |
+------+-----------+------------+
|  2   |    200    | 01/02/2019 |
+------+-----------+------------+  
|  3   |    500    | 01/01/2019 |
+------+-----------+------------+  
|  5   |    500    | 01/05/2019 |
+------+-----------+------------+

Here's my batch update query:

Update tbl.myTable set Amount = 0 where Date = '01/01/2019'

with the query it will update/affect the two data with ID 1 and 3. How can I get those ID to insert it in another table (tbl.history)?

sql-server sql
3 Answers

Use the OUTPUT clause. It provides you with a "table" named deleted which contains the values before the update, and a "table" named inserted which contains the new values.

So, you can run 

Update tbl.myTable set Amount = 0
output inserted.*,deleted.*
where Date = '01/01/2019'

To understand how it works, succeeding this, you can now create a temporary table and OUTPUT the fields you want INTO it:

Update tbl.myTable set Amount = 0
output inserted.*,deleted.* into temp_table_with_updated
where Date = '01/01/2019'
user_58259630

You can do this by using OUTPUT

declare @outputIDs as TABLE
(
   ID int
)

Update tbl.MyTable Set [Amount] = 0
OUTPUT INSERTED.ID into @outputIDs
WHERE [Date] = '01/01/2019'

The @outputIDs table will have the two updated IDs.

user_56416190

Use a caching mechanism (table variable, cte etc)

declare @temp table (id int)
insert into @temp select id from tbl.myTable where Date = '01/01/2019'
update tbl.myTable set Amount=0 where id in (select id from @temp)
-- do more stuff with the id's
user_31799570
Related