Solved

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

Posted on 2004-03-22
5
300 Views
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.

Cheers,

Rajesh
0
Comment
Question by:raje10410
5 Comments
 
LVL 15

Expert Comment

by:OMC2000
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.
0
 
LVL 2

Accepted Solution

by:
dhenson earned 250 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)
)

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
 
LVL 2

Expert Comment

by:dhenson
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

dhenson
0
 
LVL 50

Expert Comment

by:Lowfatspread
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

 
0
 
LVL 1

Author Comment

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

Cheers,

Rajesh
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now