Bulk Loading of Parent/Child Records - SQL Server

Overview¶

This article describes a technique to bulk load rows into two tables which form a parent/child relationship. That is, the "child" table has a foreign key against the "parent" table. Furthermore, the primary key on the "parent" table is an IDENTITY field. The challenge comes in with knowing what the value of this identity field is for each child.

Notes¶

(1) It's important that this script is within a single transaction to ensure no rows are inserted in the middle of it by some other process. This would throw off the calculation of the Parent ID values.

(2) Because we need to be able to rollback the transaction in the case of an error, the TRY/CATCH construct is also important.

Sample Code¶

begin try

begin transaction

declare @ParentIdOffset int = ident_current('ParentTable')

/*--- Create Temp Table, Calculating Parent IDs ---*/
/* This temp table will contain one row for each parent row to be created */
select
    ParentId = row_number() over (order by SomeField) + @ParentIdOffset
    /* other fields */
into
    #MyTempTable
from
   /* tables */

/*--- Create Rows in Parent Table ---*/
set identity_insert dbo.ParentTable on

insert into dbo.ParentTable (
     Id
    /* other fields */
    )
select
     Id = a.ParentId
    /* other fields */
from
    #MyTempTable a

set identity_insert dbo.ParentTable off

/*--- Create Rows in Child Table ---*/
insert into dbo.ChildTable (
     ParentId 
    /* other fields */
    )
select
    ParentId = a.ParentId
from
    #MyTempTable a
   /* join other tables as needed */

/* TODO: Change this to COMMIT! */
rollback transaction

end try
begin catch

    rollback transaction

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR (@ErrorMessage, @ErrorSeverity, 1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );

end catch