how to get sort the rows in the order they were inserted?
-
Hi all,
I have a table without auto generated ID or time stamp.
I want to retrieve all the rows that are inserted in the table after a certain row.
How can I do that?
-
or i can explain it clearer in the following way:
I have a table without auto-generated ID nor time stamp.
I want to identify the newly inserted rows in a table.
I can't make any modifications to the table.
-
Hi Ahmed,
Let me see if I get your question right...?
You want to make a "insert into" statement in a existing table ?
And in this case you want to identify the new rows in the last transaction?
And eather you don't have a uniq key for every record (ID or datetime)?
Why do you not use the function "identity", its easily to use...
Anyways which data do you have in the table... maybe I can do something smart out of it?
aem
-
If your table does not contain something that increases for every row, then you cannot do what you are asking for.
-
If applicable, you could create an insert trigger which would then allow you access to the inserted tables... but this would only apply for a single insert.
IE:
Insert into ... ... ...
--puts X rows in table
(assuming you dont know what X is because it was generated from a select)
it will be in #inserted inside the trigger.. but as others have said, without a datetime or identity column what you want to do isn't possible... i dont think.
-
The concept of row order just does not exist in sql server. Chris is correct! Under the circumstances you describe there is just no way you can reliably determine the order in which the existing rows have been inserted.
If your table has a PK or something that can be guaranteed to be unique then the following may be an option for rows inserted in the future. Create a second table that is linked to the first by the unique attribute and use an auto id or datetime column in the second table to track when it was inserted. You can use a trigger or application code to insert into the second table for each insert into the first table. You'll also have to consider how to handle modifications and deletions to the first table.
-
thank you all
-
Hello Ahmed,
If you have got the solution, please do post it here...i would love to learn this too....
Thanks.
-
It's already been posted in the previous messages... even if it's not crystal clear
. -
no I didn't get a solution..
I had to use the trigger method.
So, We must modify the DB which what what my manager tried to avoid.
however, thank you all because you made this clearer for me (crystal
) -
We can't provide a solution when none exists... we often have to resort to such workarounds.
-
This is not a complex or difficult thing to do, however, you can only accomplish it for the future. Creating an identify key will fill every row with an identity no matter if it did not previously exist. I use an identity field and the ID which normally is the Primary Key. I know you said you couldn't change the data. That happens if either you don't have a primary key or don't have the permissions.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply