INSERT records in specific order

  • I may be showing my lack of understanding here but will ask anyway.

    I am using a 'staging' table to assemble records for a file transfer. Found it initially easier to use a stored procedure with a cursor to read a master record, write it to the staging table, then read and write one or more detail records and repeat the process for as many master records there are. The last record written in the staging table is a 'trailer' to indicate a complete file with a record count. I begin by issuing a truncate table to clear any previous memory as I thought this to be the initial problem and it worked for awhile. The staging table has a single field of type varchar(310) with data filling specific positions as it is sent to an old legacy system. Problem is the trailer record has begun to appear in the middle of the table which invalidates the resulting file. Does anyone have ideas about ensuring the order the inserts go into this table. Sorry forthe length of the post

  • Add an identity column. Truncating it will reset the sequence back to 1 each time, as you append records they will get the next id, trailer record should have the highest. Then just export sorted on the ident col, without exporting the column of course, that should do it.

    Andy

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply