When I insert a row of data into a table via a stored procedure, the row SOMETIMES gets inserted at the bottom, and sometimes gets inserted at another random location (top, midding, etc). I am using Microsoft SQL Server.
Dea anybody know the cause of this? Or how to ensure that the row gets inserted at the bottom?
I am a novice SQL Server user, but this seems like a simple problem that the more advanced users solved long ago. Thanks in advance!This behavior you are seeing is by design. SQL Server decides where to put the data and we have no control over that. Nor, really, is it important.
If you need the data to come out of the database in a particular order, you need to use an ORDER BY statement. If it is important for you to know the order in which records were inserted into the database, I suggest using a DateAdded column with a Default Value of GETDATE() so that SQL Server will automatically populate this for you. You might also consider using a column flagged as an IDENTITY for this purpose.
Terri|||Thank you for the response. I tried your advice and it worked well. However, I noticed only one problem.
When it inserts the date, using a Default Value of GETDATE(), and using data type smalldatetime, the field gets inserted as "1/17/2005 2:23:00 AM".
However, when I set the data type to vnarchar, the field gets inserted as "Jan 17 2005 2:23AM"
I prefer the second method, as it spells out the first 3 letters of the month. The problem, however, is that the ORDER BY statement does not accurately pull the records when stored as nvarchar. It is unable to distinguish between AM and PM and pulls the records in order of numeric chronology only.
Do you know how I can fix this, and still store the items as "Jan..."?
Thanks|||Use a datetime data type to store datetime data. Use output formatting to display the data in the desired format (either by using CAST or CONVERT, or in the ASP.NET front end).
Terri
No comments:
Post a Comment