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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

914 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

22 Experts available now in Live!

Get 1:1 Help Now