gistfile1.sql

CREATE TABLE [dbo].[Job](
	[JobId] [int] IDENTITY(1,1) NOT NULL,
etc...

--One or more Splits per Job
CREATE TABLE [dbo].[Split](
	[SplitId] [int] IDENTITY(1,1) NOT NULL,
	[JobId] [int] NOT NULL,
etc...

--One or more DPVCommercialReceivingAgent records per Split
CREATE TABLE [dbo].[DPVCommercialReceivingAgent](
	[DPVCommercialReceivingAgentId] [int] IDENTITY(1,1) NOT NULL,
	[SplitId] [int] NOT NULL,
etc...

--The goal is to write a procedure that given a JobId creates a duplicate Job record, 
--and a duplicate of every child record for a job (with the new JobId from @@IDENTITY).  
--That is to say, if there are 3 splits to a job, then each split gets copied.  
--Same goes for all child records of a split.
--What is the most straightforward way of doing this?