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
331 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

679 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