We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

raje10410
raje10410 asked
on
Medium Priority
372 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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
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
CERTIFIED EXPERT
Top Expert 2011

Commented:
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

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

Cheers,

Rajesh
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.