Sample Header Ad - 728x90

Can I use OUTPUT from an UPDATE in an INSERT?

2 votes
2 answers
5790 views
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.
Asked by mnemotronic (205 rep)
Feb 26, 2020, 03:34 PM
Last activity: Feb 28, 2020, 05:28 AM