• Nice example of set-based data processing.

    I have a few minor notes here:

    1)

    SELECT @Client_ID = ISNULL(IDENT_CURRENT('NT_Client'),0) + 1

    SELECT @OrderHeader_ID = ISNULL(IDENT_CURRENT('NT_OrderHeader'),0) + 1

    Note the use of ISNULL - this allows you to provide an initial identity value even if the table is empty.

    The IDENT_CURRENT() function returns the identity seed value if a table is empty. It can be shown by this example:

    create table #test (id int identity (10,3))

    select ident_current('#test') -- the table is empty, the result is 10

    insert #test default values -- the table has one row, the result is 10

    select ident_current('#test')

    insert #test default values

    select ident_current('#test') -- the table has two rows, the result is 13

    This behavior is identical at least in MSSQL 2000, 2005, and 2008.

    2) It's better to insert data into the tables in one transaction:

    BEGIN TRANSACTION

    INSERT INTO dbo.NT_Client ...

    INSERT INTO dbo.NT_OrderHeader ...

    INSERT INTO dbo.NT_OrderDetail ...

    COMMIT TRANSACTION

    If something fails during the inserts, we'll not get incomplete data (order headers without order details etc.).

    3) If data are loaded to the system that is currently in use and someone adds new rows to the tables, you may receive an error like this: "Violation of PRIMARY KEY constraint 'PK_NT_OrderHeader'. Cannot insert duplicate key in object 'dbo.NT_OrderHeader'."

    For example, you have orders with header IDs = 1, 2, 3, get @OrderHeader_ID = 4, then prepare #Tmp_SourceData. The value '4' will be amongst the header IDs in #Tmp_SourceData. At the same time some user creates a new order with Header_ID = 4. You'll receive the above error message while inserting into dbo.NT_OrderHeader.

    This issue may be avoided in this way (supposing that no more than 1000 clients and 10000 orders may be created by other users during our data load):

    SELECT @Client_ID = IDENT_CURRENT('NT_Client') + 1000

    SELECT @OrderHeader_ID = IDENT_CURRENT('NT_OrderHeader') + 10000

    Edit: grammar error