0

Imagine table A like this:

create table A (
        ID int primary key identity(1, 1),
        Name varchar(20)
)

and table B:

create table B (
        ID int primary key identity(1, 1),
        A_ID int foreign key references A(ID)
        Name varchar(20)
)

As I want to keep data even after deleting them, I want to have 2 tables called A1 and B1 in order to store an archive of old data like these:

create table A1 (
        ID int primary key identity(1, 1),
        Name varchar(20),
        OldID int
)
create table B1 (
        ID int primary key identity(1, 1),
        A1_ID int foreign key references A1(ID),
        Name varchar(20),
        OldMasterID int
)

As you know whenever I want to send an A row I'll insert it and get the inserted ID using some tools like scope_identity() function in MS SQL Server. But how to transfer several rows and take the inserted ID in order to set as master's ID?

A solution is to use a loop like "while" and insert rows one by one. But I don't think about this solution a good performance way. Is there another way to do this?

Shaho
  • 182
  • 2
  • 14
  • There's no master/detail in relational databases. There are tables and foreign key *constraints*. What you describe though isn't even that - those are archive or history tables. In SQL Server 2016 and later you get them out-of-the-box as temporal tables. If you google for `history table` you'll find a lot of ways to implement the same thing using eg triggers. Which SQL Server version are you using? – Panagiotis Kanavos Jan 07 '20 at 11:50
  • 1
    Even with older versions though, assuming the history table contains only deleted items, it's the "old" IDs that should be used as keys. Just remove the `IDENTITY (1,1)` part from the keys and copy the deleted records directly to the history tables. – Panagiotis Kanavos Jan 07 '20 at 11:51
  • In a trigger you get the modified/deleted rows through the [inserted and deleted](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-ver15) tables – Panagiotis Kanavos Jan 07 '20 at 11:53
  • I would have written an answer for this, but you can have a look over the `OUTPUT` clause combined with the `INSERTED` or `DELETED` tables. – Radu Gheorghiu Jan 07 '20 at 11:53

0 Answers0