2

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.

6

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
|improve this answer|||||
1

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.