Thread: Insert into and Sequence

Hi to all,

executing an INSERT INTO like this:
           INSERT INTO "_Tests_Steps"           (                  "New_Test_Step_Id",                  "Test_Step_Id",
                "Process_Test_Id",                  "Step_Id",                  "Sequence_Id",
"StepGroup_Id",                 "Skip"           )           SELECT
nextval('"Tests_Steps_Test_Step_Id_seq"'),                 "Test_Step_Id",                  B."Process_Test_Id",
         "Step_Id",                  "Sequence_Id",                  "StepGroup_Id",                  A."Skip"
FROM "_Processes_Tests" B INNER JOIN "Tests_Steps" A           ON A."Process_Test_Id" = B."Process_Test_Id"
WHERE"Version_Id" = vers_id           ORDER BY "Test_Step_Id";
 
...............         where "_Tests_Steps" and "_Processes_Tests" are both temporary 
tables, in the resulting "_Tests_Steps table records are not sorted in 
the expected mode.
In other word, I need to extract records from "Tests_Steps" and then put 
them in "_Tests_Steps" ordered exactly as they were  inserted first time.
This because I need to duplicate this records, assigning them a new 
serial value. This records are later binding to others records of 
different tables.

The resulting recordset is the follow:

"New_Test_Step_Id",  "Test_Step_Id", "Process_Test_Id", "Step_Id", 
"Sequence_Id", "StepGroup_Id",  "Skip"
2332;                            2136;                
2225;                     958;         404;                   
363;                     f
2331;                            2137;                
2225;                     959;        404;                    
363;                     f
2334;                            2285;                
2225;                     960;        404;                    
363;                     f
2333;                            2286;                
2225;                     961;        404;                    
363;                     f


Apparently, new records are not inserted in the table according to the 
order selected by "Test_Step_Id" field;

Any suggest??

Thanks in advance

Luigi Pirillo

<blockquote cite="mid:4766CE38.6060904@gmail.com" type="cite"><tt><br /> Apparently, new records are not inserted in
thetable according to the order selected by "Test_Step_Id" field; <br /></tt></blockquote><tt>As seen in: <a
class="moz-txt-link-freetext"href="http://en.wikipedia.org/wiki/SQL:">http://en.wikipedia.org/wiki/SQL:</a><br /> "The
orderof rows returned by an SQL query is never guaranteed unless an <code>ORDER BY</code> clause is specified".<br
/><br/> In other words, a database system does not guarantee you the order in which table rows are stored. So, the
orderin which you insert your rows is not the one which has to be followed when selecting them afterwards. You must
specifyby "ORDER BY" how you want to sort your recordset.<br /><br /> It seems you need to revisit the concept of your
system.<br/><br /> Best regards<br /><br /> Irek<br /></tt><br />