SQL-Copy a record with its related child records

Question

Hi,

I want to Copy a Row in SQL which has child records in 2 other tables. i want to duplicate them all if the master record duplicates.

for example if i say i want to copy master record with id 15 in to a new record. i want all its child records to be duplicated with the new master record id as well. can do it through TSQL using loops and conditions. but i just want to know if there are better options.

Thanks.

Answer 1

The best option is to use an INSERT trigger on your master table that obtains the corresponding older row's children and inserts them in the child table.

I'm very rusty at triggers, but something like this:

CREATE TRIGGER tr_master ON master FOR AFTER INSERT AS
   INSERT INTO childtable(masterid, childvalue)
      SELECT inserted.id, oldchildren.childvalue
      FROM inserted INNER JOIN childtable oldchildren
         ON oldchildren.masterid = inserted.copiedfromid
      )

Since, when the trigger occurs, it doesn't "know" which master record the current inserted record(s) were copied from, you'll need to track the primary key of the original record in a column in the master table (which I've labeled "copiedfromid" here).

"Inserted" is a special table available within a trigger that contains all of the rows being inserted into your master table in that transaction. I used "FOR AFTER INSERT" rather than plain old "FOR INSERT" because I'm assuming your child table's foreign key enforces referential integrity with the master table, so I think you have to only trigger this action after the insert actually occurs.

Answer 2

EDIT Changed to better match requirements of OP.

If you are only copying one master record at a time. Then use a stored procedure to copy the master and children. Include error handling and transaction control on the stored produre. The copying code would be insert statements like so:

  declare @NewMasterId int -- or the appropriate type.
  insert into MasterCopy (column names ...)
  select column names ..
  from Master
  where MasterId = @MasterId
  set @NewMasterId = scope_identity

  insert into ChildCopyA (MasterId, column names ...)
  select @NewMasterId, column names ...
  from ChildA 
  where MasterId = @MasterId -- FK to master table.

  insert into ChildCopyB (MasterId, column names ...)
  select @NewMasterId, column names ...
  from ChildB 
  where MasterId = @MasterId -- FK to master table.