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

Posted on 2004-03-22
Medium Priority
Last Modified: 2006-11-17
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.


Question by:raje10410
LVL 15

Expert Comment

ID: 10655748
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.

Accepted Solution

dhenson earned 1000 total points
ID: 10655814
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)


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

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.

Expert Comment

ID: 10655823
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

LVL 50

Expert Comment

ID: 10655993
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


Author Comment

ID: 10656442
Thanks dhenson. Your idea worked out pretty well!!!



Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question