0

What I have is an array of objects and I need to update the DB layer based on that array.

If the object contains id property - Update query, if it doesn't - Insert query.

What I end up with is

BEGIN TRY
BEGIN TRANSACTION
UPDATE {...}
INSERT INTO {...}
UPDATE {...}
INSERT INTO {...}
{...}
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
THROW
END CATCH

What can I do so that as a result of this query I can then fetch every row that has been affected (either modified or inserted) ?

Pavlin Petkov
  • 1,022
  • 3
  • 17
  • 38
  • Have you tried MERGE? – Vitaly Borisov Jul 04 '19 at 09:46
  • 1
    And, in addition to `MERGE`, have you have a look at the `OUTPUT` clause? – Thom A Jul 04 '19 at 09:46
  • Not sure what MERGE does, as for OUTPUT - I will check if I can use it for all of these at the same time – Pavlin Petkov Jul 04 '19 at 09:49
  • yeah You should start implementing MERGE for this kind of query.. – IShubh Jul 04 '19 at 09:59
  • 1
    [OUTPUT (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017) & [MERGE (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017) – Thom A Jul 04 '19 at 10:07
  • Checked out MERGE and it is concerning 2 tables or more, in my case I only have 1 table which I modify over and over. I think OUTPUT should be able to work out. @Larnu if you make an answer I will approve it as by what I read it should be exactly that. Thank you! – Pavlin Petkov Jul 04 '19 at 10:49
  • *"Checked out MERGE and it is concerning 2 tables or more"* Huh? You perform a `MERGE` against 1 table (the sources can be many if you wish, but there is only one destination object); A DML statement can't effect more than one table at a time. You've misread the documentation I'm afraid. – Thom A Jul 04 '19 at 11:08
  • Can I output the results of both UPDATE and INSERT into a temp table so that I can order them and stuff with OUTPUT? – Pavlin Petkov Jul 04 '19 at 12:29

0 Answers0