SQL to copy record and related records to the same table

To do this you will need to use INSERT...SELECT, SCOPE_IDENTITY/@@IDENTITY and MERGE. The example below should help:

DECLARE @Quote TABLE
(
    ID INT IDENTITY(1, 1),
    RandomText VARCHAR(20)
)

DECLARE @Assembly TABLE
(
    ID INT IDENTITY(1,1),
    QuoteID INT,
    RandomText2 VARCHAR(30)
)

DECLARE @Part TABLE
(
    ID INT IDENTITY(1, 1),
    AssemblyID INT,
    RandomText3 VARCHAR(40)
)


DECLARE @Temp TABLE
(
    OldAssemblyID INT,
    NewAssemblyID INT
)

DECLARE @NewQuoteID INT

INSERT INTO @Quote (RandomText) VALUES ('Quote 1') 
INSERT INTO @Quote (RandomText) VALUES ('Quote 2') 

INSERT INTO @Assembly (QuoteID, RandomText2) VALUES (1, 'Quote 1 Assembly 1')
INSERT INTO @Assembly (QuoteID, RandomText2) VALUES (1, 'Quote 1 Assembly 2')
INSERT INTO @Assembly (QuoteID, RandomText2) VALUES (1, 'Quote 1 Assembly 3')
INSERT INTO @Assembly (QuoteID, RandomText2) VALUES (2, 'Quote 2 Assembly 1')
INSERT INTO @Assembly (QuoteID, RandomText2) VALUES (2, 'Quote 2 Assembly 2')
INSERT INTO @Assembly (QuoteID, RandomText2) VALUES (2, 'Quote 2 Assembly 3')

INSERT INTO @Part (AssemblyID, RandomText3) VALUES (1, 'Quote 1 Assembly 1 Part 1')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (1, 'Quote 1 Assembly 1 Part 2')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (1, 'Quote 1 Assembly 1 Part 3')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (2, 'Quote 1 Assembly 2 Part 1')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (2, 'Quote 1 Assembly 2 Part 2')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (2, 'Quote 1 Assembly 2 Part 3')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (3, 'Quote 1 Assembly 3 Part 1')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (3, 'Quote 1 Assembly 3 Part 2')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (3, 'Quote 1 Assembly 3 Part 3')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (4, 'Quote 2 Assembly 4 Part 1')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (4, 'Quote 2 Assembly 4 Part 2')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (4, 'Quote 2 Assembly 4 Part 3')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (5, 'Quote 2 Assembly 5 Part 1')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (5, 'Quote 2 Assembly 5 Part 2')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (5, 'Quote 2 Assembly 5 Part 3')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (6, 'Quote 2 Assembly 6 Part 1')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (6, 'Quote 2 Assembly 6 Part 2')
INSERT INTO @Part (AssemblyID, RandomText3) VALUES (6, 'Quote 2 Assembly 6 Part 3')

INSERT INTO @Quote (RandomText)
SELECT RandomText FROM @Quote WHERE ID = 1

SELECT @NewQuoteID = SCOPE_IDENTITY()

MERGE @Assembly NewAssembly
USING (SELECT ID, @NewQuoteID NewQuoteID, RandomText2 FROM @Assembly WHERE QuoteID = 1) AS OriginalAssembly
ON 0 = 1
WHEN NOT MATCHED BY TARGET THEN
INSERT (QuoteID, RandomText2) VALUES (OriginalAssembly.NewQuoteID, OriginalAssembly.RandomText2)
OUTPUT OriginalAssembly.ID, Inserted.ID INTO @Temp (OldAssemblyID, NewAssemblyID);

INSERT INTO @Part (AssemblyID, RandomText3)
SELECT T.NewAssemblyID, P.RandomText3
FROM @Part P
    INNER JOIN @Temp T ON P.AssemblyID = T.OldAssemblyID

Select allOpen in new window

Not to be "that guy," but is there a reason that you're copying assemblies, or assembly items? I would imagine that if you're doing this, then your data isn't normalized.

Let's suppose there is a quote in the database for 3 Widgets. These Widgets are your assemblies, and to keep things super simple, let's say that a Widget assembly is made of two Gadget assembly items. In your Assemblies table, are you going to have three identical records for Widgets? And then in your assembly items table, are there going to be two identical Gadget records for each Widget? We're now looking at 6 identical records in your assembly items table for a single quote. When you copy a quote, and then copy all of the related items, are there now going to be 12 identical records?

Thanks. Lets suppose you are an engineer and a potential customer calls in with a request for quote. Knowing that we sell pumping stations that can consist of hundreds of assemblies that contain thousands of parts, would you rather create a quote from scratch or start from a predefined template or from a quote that is almost the same and only needs a couple of parts swapped out or pricing adjusted? So the copied quote will provide the engineers as a starting point for new quotes. It will also be used to created revisions. So when the customer changes his mind about something, we can just create a copy of the current quote, increment the revision and then they can make the changes required in the new revision. Wecan't simply point to the assemblies table and say that assembly is part of this quote because they have to be able to change anything about that assembly. Add parts, remove parts, adjust pricing, or quantities. The only thing that won't change is the assembly name.

Sorry this comment is a bit late. I appreciate the thinking behind this design, but if your typical quotes and assemblies are as large as you say, then I would worry that this approach would impact the size and performance of your DB in a non-trivial way. You don't need to create copies of all of the assemblies, and all of the parts to allow this sort of flexibility. I think you will find that your database will be noticeably smaller, and perform much better, if you start by making a copy of the quote record, and only the quote record.

When an engineer adds, removes, or changes parts in an assembly in that quote, you insert a new record into your assemblies table, and then run an update query to modify the quote. Likewise, rather than copying all of the part records associated with a quote when an engineer uses an existing quote as a starting point, you only need to copy a part record when a change is made to a specific part. Once again, run an update query on the assemblies table to modify the assembly definition to use the new part definition.

This architecture, if your numbers are typical, will reduce the number of insert operations on your database by multiple orders of magnitude in cases where your engineers make few, if any changes to the definition of a quote. This architecture also has the advantage of allowing you to track the evolution of a part over time; by adding a field "derived from," you can show that the "Bar" part evolved from the "Foo" part.

I've attached a photo of a very crude visual aide I created to show you how you can solve both issues at once. For extra savings, when someone attempts to modify, or create a new part or assembly, I would search the DB for any records that match the user's specs exactly, and recycle that ID if you can.

Found a source from another site that I used as a template to figure out my own problem