How to Retrieve Rows exactly in the same order in which they have been inserted into SQL Server?

It's been really bothering. I have to generate an interface file from my system. This will have @@HDRxxxx as the first row, then some detail rows, then @@TLRxxxx as the last row. I have a table into which I insert rows in this order and BCP it to a text file which in turn is supplied to the host system. Now, my problem is, at times the @@TLRxxxx row appears somewhere in the middle of the rows when I use the Select statement to retrieve rows. I can't use index or order by clause. If I had a rowid concept I could have used the order by clause. Can someone help me with your valuable inputs/ideas in the resolving this issue? Thanks in advance.

Cheers,

Rajesh
LVL 1
raje10410Asked:
Who is Participating?
 
dhensonConnect With a Mentor Commented:
There are a number of ways to do this... I would probably handle this in a stored procedure... Forgive me for any syntax errors as I'm not at the office and don't have a SQL server handy to try this on.


Assuming you only need one header (at the beginning) and one Trailer (at the end):

CREATE PROCEDURE ExportInterface (
@fileOut varchar(100)
)

AS

begin
declare @tempDataOut varchar (100)
declare @execStr varchar(1000)
declare @query varchar(1000)

set @tempDataOut = 'c:\tempdatafile.txt'

     --Create the Output file and insert the Header
set @execStr = 'echo "@@HDRxxxx">>' + @fileOut
exec master..xp_cmdshell @execStr

     --Create the data file from your query (modify the below query as needed)
     -- if you need to use a single quote in your select, you will need to escape it with a single quote
set @query = 'select ''your data'', otherdata from YourTable'
set @execStr = 'BCP "' + @query + '" queryout ' + @tempDataOut
exec master..xp_cmdshell @execStr

     --Append the data to the Output file
set @execStr = 'type ' + @tempDataOut + '>>' + @fileOut
exec master..xp_cmdshell @execStr

     --Finish the output file by appending the Trailer
set @execStr = 'echo "@@TLRxxxx">>' + @fileOut
exec master..xp_cmdshell @execStr

     -Clean up by Deleting the tempDataOut file
set @execStr = 'del ' + @tempDataOut
exec master..xp_cmdshell @execStr
end

Anyway... you get the picture.

I'm sure you could probably use UNION ALL (I'm not really familiar with the order rules of a UNION statement) and 3 seperate queries, but I can't test that from here to know if the order of the queries is maintaned.
0
 
OMC2000Commented:
There are no other ways to order output recordset rather than ORDER BY or Index. You could either introduce identity column and order by it, make datetime column with the default value equal to getdate() function result and order by it.
0
 
dhensonCommented:
you could then execute the above procedure by

exec ExportInterface 'c:\outputfilename.txt'

you could schedule it from a DTS package by inserting the above into a SQL Task

dhenson
0
 
LowfatspreadCommented:
i you want order
you have to design for it

either have an identity column in the table

or tell us how the internal order of the data can be determined from the row themselves...

if you have an unordered table/select statement then the retrieval order is not guaranteed each time you perform the request

 
0
 
raje10410Author Commented:
Thanks dhenson. Your idea worked out pretty well!!!

Cheers,

Rajesh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.