Force Insert as Last Record

[DBAchick]

Basically, I need to control the position of a record I am inserted.

I have created TableX with a single column (no index) varchar(8000).

Main Stored Procedure calls
Stored Procedure 1 which inserts a 'header record' in TableX. The calls Stored Procedure 2 which queries several other tables, orders the records, concatenates each column value into a single record and inserts those 'detail' records into TableX. Finally, calls Stored Procedure 3 which calculates a count the 'detail' records and inserts a 'trailer record' into TableX.

Problem: Randomly, the 'trailer record' gets inserted within the detail records. I added a waitdelay between SP2 & SP3 to no avail.

Is there a way to FORCE the results of SP3 to be inserted as the last physical record in TableX?

 

[RobertT687]

Basically the answer is 'NO'. In a relational database table, especially one with no index, there is no such thing as First or Last record. The DBMS is going to place the inserted row whereever there happens to be space on the page. The only way I know of to control the position is through the use of a Clustered Index and make certain the key to the Last record always has the highest sort order within the index. The 'order' of the rows can be controlled by the front-end using an ORDER BY clause.