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