0

I am inserted a row from A to B. I am trying to populate a temporary table with the id of created rows and original rows

DECLARE @Ids TABLE ([OldId] INT, [NewId] Int)

INSERT INTO B
  (code,
   type)

OUTPUT NULL,insterted.ID into @Ids

Select  @NewCode, type
From A
Where code = @OldCode

I can get the inserted rows ids via the insterted token, but not the origin.

How can i build this Ids table, either using or not the OUTPUT token ?

Foxhunt
  • 764
  • 1
  • 9
  • 23
  • 1
    What RDBMS do you use? – Jonas Metzler Aug 31 '23 at 13:31
  • I use SqlServer – Foxhunt Aug 31 '23 at 13:34
  • 1
    One way https://stackoverflow.com/q/5365629/73226 – Martin Smith Aug 31 '23 at 13:45
  • 1
    (Unfortunately) `MERGE` is what you're going to need to use if you want to `OUTPUT` columns not from the target. – Thom A Aug 31 '23 at 13:51
  • 1
    https://dbfiddle.uk/umr54ZW5 – Charlieface Aug 31 '23 at 13:58
  • Primary key values, especially numeric IDs, don't (shouldn't) change. Why do you want an `OldId`? What are you trying to do? There are probably better solutions – Panagiotis Kanavos Aug 31 '23 at 13:58
  • If you want to track changes all the changes since the last operation, or the changes made in a single large operation, you may be able to use SQL Server's change tracking. This tracks the PK values along with the change reason (Insert, Update, Delete) by "version", which allows you to retrieve all changes since the last time you checked – Panagiotis Kanavos Aug 31 '23 at 14:00
  • 1
    PK does not change, but a new one is created in B when i insert into B. I would like to be able to link a B entry to its A entry (and not the opposite !), so i would like a way to build this ids table, either using or not `OUTPUT` – Foxhunt Aug 31 '23 at 14:06

0 Answers0