Can I use OUTPUT from an UPDATE in an INSERT?

Question

I want to add a record to my app's "SystemSettings" table and set the PK value using the value from an UPDATE. The PK value comes from the "TS_LASTIDS" table which contains the maximum PK value for each table in this application (MicroFocus SBM). I need to increment the "TS_LASTID" column in that table and use the new value as the PK when INSERTING a new record into the "SystemSettings" table.

Insert Into
   ts_SystemSettings ( ts_Id, ts_Name, ts_LongValue)

Values (
   ( -- ******************** This subquery updates the PK in TS_LASTIDS and outputs the new value
      Update
         ts_LastIds
      Set
         ts_LastId=ts_LastId+1
      Output
         INSERTED.ts_LastId
      Where
         ts_Name = 'SystemSettings'
   ) ,  -- ******************** 
   'NSLastChangeId' ,
   1 ,
) ;

I can't figure out the syntax. This is MS SQL server 2012.

Answer 1

Here is an example of using OUTPUT...INTO

--demo setup
drop table if exists InsertTest;
Create Table InsertTest (id int);

drop table if exists UpdateTest;
Create Table UpdateTest (id int);
insert into UpdateTest(id) values(1),(2);

--solution
UPDATE UpdateTest
SET id = id + 1
OUTPUT INSERTED.id
INTO InsertTest;

--verify results of insert
select * from InsertTest

---

ID
2
3

Answer 2

Adding INTO solved part of the problem.

The trick is to move the INSERT operation "into" the OUTPUT INTO clause. The INSERT is "implied", I think because OUTPUT INTO does an INSERT. This only works because I want to do an INSERT with the new ID -- if I had wanted to do an UPDATE this would not work.

Update
   ts_LastIds
Set
   ts_LastId=ts_LastId+1
Output 
   INSERTED.ts_LastId ,
   'NSLastChangeId' ,
   1
Into ts_SystemSettings ( ts_Id, ts_Name, ts_DataType, ts_LongValue )
Where
   ts_Name = 'SystemSettings' ;

So there you have it; calculate new PK value; update one table with that value, and use new value in an INSERT; all in 1 atomic statement.

Documentation: Inserting Data Returned From an OUTPUT Clause Into a Table