Nice example of set-based data processing.
I have a few minor notes here:
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
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:
INSERT INTO dbo.NT_Client ...
INSERT INTO dbo.NT_OrderHeader ...
INSERT INTO dbo.NT_OrderDetail ...
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') + 1000Edit: grammar error
SELECT @OrderHeader_ID = IDENT_CURRENT('NT_OrderHeader') + 10000