INSERT-SELECT depending on the Select:ed order

I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.

However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?

Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).This sure looks like a "tip of the iceberg" thread...

You can include an ORDER BY statement in your INSERT code, at the end of statement where you would normally place it.

Your trigger is only going to fire ONCE per transaction, no matter how many records are in the transaction. Insert 1000 individual records and the trigger fires 1000 times, but do a single insert of 1000 records and your trigger will only fire once.

Now the big question...what the heck are you doing that requires ordered inserts into a view with an INSTEAD OF trigger? Such complexity is rarely necessary.|||Thanks; I noticed that the records "sent" from the SELECT to the INSERT appeared in reversed order. And, I though the trigger was fired once per record. I've created an SP to take care of it.

The requirement on the order is because this table keeps track of how users logon to an application and then logoff. I'm changing the table from a structure where each such event has its own table row, to a structure where both events are stored in the same row (one datetime column for the logons and one for the logoffs). When transferring data from the old table to the new, I'm assuming that the Logon records are read before the Logoff records, since the Logon records mean an INSERT into the new table while the Logoff records mean an UPDATE of an already existing row (having a Logon date registered).|||You should probably just include a datetime value in your recordset rather than relying on record order.

inserting text after a certain record of a recordset

What I'm trying to accomplish is to add some text after the 5th
dispalyed record of a recrod set, I tried using absolute position and
the following:
<%
if rs.absoluteposition = 5 then
Response.Write "Testing"
end if
%>
I'm probably missing something or there is some better way to do this,
thanks for any assistance in advance.counter = 0
do while not rs.eof
counter = counter + 1
if counter = 5 then
response.write "testing"
end if
rs.movenext
loop
<dabootleg@.gmail.com> wrote in message
news:1134328540.390884.113180@.g47g2000cwa.googlegroups.com...
> What I'm trying to accomplish is to add some text after the 5th
> dispalyed record of a recrod set, I tried using absolute position and
> the following:
> <%
> if rs.absoluteposition = 5 then
> Response.Write "Testing"
> end if
> %>
> I'm probably missing something or there is some better way to do this,
> thanks for any assistance in advance.
>