0

I'm working with a table that keeps tracks with all changes so if there is a change, the old record of the client is end dated and a new record is created with a copy of the new values.

What I'm trying to do is:

  1. Create a copy of the old record into a new record but increment the primary key
  2. Make updates to the new record
  3. End date the old record

My main challenge is how do I

  • Create a copy of the old record
  • And create a new key
  • Without having to list all the columns manually

I can create a copy of the record no problem.

INSERT INTO SCHEMA.TABLE
SELECT * FROM SCHEMA.TABLE WHERE ID=XX

However the table has a primary key so I can't exactly just make a copy. Also, since I want to update the copied record, I want to store the key as well.

I could write the below code, but there are a lots of columns.

INSERT SCHEMA.TABLE (Col1, Col2)
VALUES(NEXT VALUE FOR SCHEMA.COL1,VAL2)

And lastly I want to add my update my record so I need to capture the value of the new key. This isn't a problem as long as I have the new key.

Any suggestions would be appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
rac3r5
  • 1
  • Use the `OUTPUT` clause to obtain the new id and use it to update the new record. As for "I could write the below code, but there are a lots of columns." - thats life I'm afraid. – Dale K Oct 28 '21 at 22:06
  • if the primary key is `IDENTITY` then you can insert then do `SELECT SCOPE_IDENTITY()` which will return the new key. – iSR5 Oct 28 '21 at 22:16

0 Answers0