Need some SQL help to copy a record and related records into the same tables

Question

I have records in 3 tables that are related to each other. I am trying to copy the record to give the user a starting point for completing the form rather than starting from scratch. I have a stored procedure that works somewhat.

The top level record can have many related records in the middle level. I can copy the top level record no problem and retrieve the ID of the new record. I can also copy the middle level records and add it to the table using the new ID from the first step. The problem is that the middle level records also have many records in the third level. I don't know how to copy the third level records into each of middle level record it belongs to. I get the new ID from the middle step but it goes to the last record that was inserted so all of the third level records get put under that ID. I think the last step should be a subquery in the middle step? Here is what I have so far and thank you to anyone that can help.

ALTER PROCEDURE [dbo].[qryCopyQuote] 
-- Add the parameters for the stored procedure here
@OriginalQuoteID    INT,
@NewQuoteNumber     VARCHAR(10),
@NewQuoteID         INT OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
INSERT INTO         [tbl_QuoteDetails](
    [QuoteNumber], [Revision], [CurrentDate], 
    [QuoteDate], [Country], [StateID], [City], 
    [ProjectName], [RepCompanyID], [ApplicationEngineer], 
    [ElectricalEngineer], [MechanicalEngineer], [RevisionNotes]
)
SELECT  @NewQuoteNumber, '-', [CurrentDate], 
        [QuoteDate], [Country], [StateID], [City], 
        [ProjectName], [RepCompanyID],  [ApplicationEngineer], 
        [ElectricalEngineer], [MechanicalEngineer], [RevisionNotes]
FROM [tbl_QuoteDetails] AS QD
WHERE [QD].QuoteID = @OriginalQuoteID

SET @NewQuoteID = SCOPE_IDENTITY();

INSERT INTO [tbl_QuoteAssemblies](
    [QuoteID], [AssemblyName], [AssemblyQuantity]
)
SELECT  @NewQuoteID, [QA].[AssemblyName], [QA].[AssemblyQuantity]
FROM   [tbl_QuoteAssemblies] AS QA
WHERE  [QA].[QuoteID] = @OriginalQuoteID

DECLARE @NewAssemblyID  int
SET @NewAssemblyID = SCOPE_IDENTITY();

INSERT INTO [tbl_QuoteAssemblyDetails](
    [AssemblyID], [ItemQuantity], [PartNumber], 
    [ItemCost], [ItemDescription], [LastUpdate], 
    [VendorContractPrice], [VendorItem]
)
SELECT @NewAssemblyID, [QD].[ItemQuantity], [QD].[PartNumber], 
    [QD].[ItemCost], [QD].[ItemDescription], [QD].[LastUpdate], 
    [QD].[VendorContractPrice], [QD].[VendorItem]
FROM [tbl_QuoteAssemblyDetails] AS QD 
    INNER JOIN [tbl_QuoteAssemblies] QA ON [QD].[AssemblyID] = [QA].[AssemblyID]
WHERE [QA].[QuoteID] = @OriginalQuoteID

RETURN @NewQuoteID;

how does the original relationship looks like? can you post your sample data and expected output by any chance? — RoMEoMusTDiE, Jun 23 '17 at 00:56
you should look at OUTPUT clause of INSERT, which can give you new ids of middle table https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql — shibormot, Jun 23 '17 at 01:23
Possible duplicate of [Cascade copy a row with all child rows and their child rows, etc](https://stackoverflow.com/questions/41595802/cascade-copy-a-row-with-all-child-rows-and-their-child-rows-etc) — Vladimir Baranov, Jun 23 '17 at 06:38
@VladimirBaranov I used your answer as a template. I get no errors in the editor but when I try to execute, it says I have fewer columns in the insert statement than in the values clause of the first "block". But when I execute the first "block" alone it works fine. Do you know what could cause this? I'm not sure how to put the script in this comment window or I would post it. — Randy, Jun 23 '17 at 14:25
@RandyB., you should [edit](https://stackoverflow.com/posts/44711454/edit) your question and add these details in the question. Show the definition (DDL) of all tables (use SSMS to script `CREATE TABLE` for you). Show us the code that you are trying to run and show us the exact error message that you are getting. — Vladimir Baranov, Jun 24 '17 at 02:59

shibormot · Answer 1 · 2017-06-23T21:04:39.740

I created simple testing environment and copy_qd procedure, using the answer here

Code:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table qd ( 
    id int not null identity(1,1) primary key,
    data nvarchar(max) not null
)
create table qa (
   id int not null identity(1,1) primary key,
   qd_id int not null,
   data nvarchar(max) not null
)
create table qad (
   id int not null identity(1,1) primary key,
   qa_id int not null,
   data nvarchar(max) not null
)
GO

create procedure copy_qd
  @old_qd_id int
as
begin
    declare 
        @new_qd_id int


    insert qd(data) select data from qd where id = @old_qd_id
    set @new_qd_id = scope_identity()

    declare @a_ids table (old_id int, new_id int)

    merge qa
    using (select id, data from qa where qd_id = @old_qd_id) as old
    on     (1=0) --arbitrary join condition
    when not matched then
        insert (qd_id, data)
        values  (@new_qd_id, old.data)
        output  old.id, inserted.id 
        into @a_ids
    ;

    insert qad(qa_id, data)
    select map.new_id, old.data
    from qad old join @a_ids map on old.qa_id = map.old_id

    return @new_qd_id
end
go

set identity_insert qd on

insert qd (id, data) values (1, 'qd_1')
insert qd (id, data) values (2, 'qd_2')

set identity_insert qd off

set identity_insert qa on

insert qa (id, qd_id, data) values (1, 1, 'qd_1.qa_1')
insert qa (id, qd_id, data) values (2, 1, 'qd_1.qa_2')
insert qa (id, qd_id, data) values (3, 1, 'qd_1.qa_3')

insert qa (id, qd_id, data) values (4, 2, 'qd_2.qa_1')
insert qa (id, qd_id, data) values (5, 2, 'qd_2.qa_2')
insert qa (id, qd_id, data) values (6, 2, 'qd_2.qa_3')

set identity_insert qa off

set identity_insert qad on

insert qad (id, qa_id, data) values (1, 1, 'qd_1.qa_1.qad_1')
insert qad (id, qa_id, data) values (2, 1, 'qd_1.qa_1.qad_2')
insert qad (id, qa_id, data) values (3, 1, 'qd_1.qa_1.qad_3')
insert qad (id, qa_id, data) values (4, 2, 'qd_1.qa_2.qad_1')
insert qad (id, qa_id, data) values (5, 2, 'qd_1.qa_2.qad_2')
insert qad (id, qa_id, data) values (6, 2, 'qd_1.qa_2.qad_3')
insert qad (id, qa_id, data) values (7, 3, 'qd_1.qa_3.qad_1')
insert qad (id, qa_id, data) values (8, 3, 'qd_1.qa_3.qad_2')
insert qad (id, qa_id, data) values (9, 3, 'qd_1.qa_3.qad_3')

insert qad (id, qa_id, data) values (10, 4, 'qd_2.qa_1.qad_1')
insert qad (id, qa_id, data) values (11, 4, 'qd_2.qa_1.qad_2')
insert qad (id, qa_id, data) values (12, 4, 'qd_2.qa_1.qad_3')
insert qad (id, qa_id, data) values (13, 5, 'qd_2.qa_2.qad_1')
insert qad (id, qa_id, data) values (14, 5, 'qd_2.qa_2.qad_2')
insert qad (id, qa_id, data) values (15, 5, 'qd_2.qa_2.qad_3')
insert qad (id, qa_id, data) values (16, 6, 'qd_2.qa_3.qad_1')
insert qad (id, qa_id, data) values (17, 6, 'qd_2.qa_3.qad_2')
insert qad (id, qa_id, data) values (18, 6, 'qd_2.qa_3.qad_3')

set identity_insert qad off

--copying data
exec copy_qd 1

Query 1:

select * 
from qad
  join qa on qad.qa_id = qa.id
  join qd on qa.qd_id = qd.id
where qd.id = 3

Results:

| id | qa_id |            data | id | qd_id |      data | id | data |
|----|-------|-----------------|----|-------|-----------|----|------|
| 19 |     7 | qd_1.qa_1.qad_1 |  7 |     3 | qd_1.qa_1 |  3 | qd_1 |
| 20 |     7 | qd_1.qa_1.qad_2 |  7 |     3 | qd_1.qa_1 |  3 | qd_1 |
| 21 |     7 | qd_1.qa_1.qad_3 |  7 |     3 | qd_1.qa_1 |  3 | qd_1 |
| 22 |     8 | qd_1.qa_2.qad_1 |  8 |     3 | qd_1.qa_2 |  3 | qd_1 |
| 23 |     8 | qd_1.qa_2.qad_2 |  8 |     3 | qd_1.qa_2 |  3 | qd_1 |
| 24 |     8 | qd_1.qa_2.qad_3 |  8 |     3 | qd_1.qa_2 |  3 | qd_1 |
| 25 |     9 | qd_1.qa_3.qad_1 |  9 |     3 | qd_1.qa_3 |  3 | qd_1 |
| 26 |     9 | qd_1.qa_3.qad_2 |  9 |     3 | qd_1.qa_3 |  3 | qd_1 |
| 27 |     9 | qd_1.qa_3.qad_3 |  9 |     3 | qd_1.qa_3 |  3 | qd_1 |

Getting an error on the select line SELECT @NewQuoteID, [QA].[AssemblyName], [QA].[AssemblyQuantity] multi-part identifier' not able to be bound — Randy, Jun 23 '17 at 02:29
@RandyB. sorry I am not tested my own answer. I rewrote answer and tested it on my testing environment — shibormot, Jun 23 '17 at 21:06

Need some SQL help to copy a record and related records into the same tables

1 Answers1