SQL INSERT command is not putting data at the end of the table?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
DaveV
Posts: 4
Joined: Thu 20 Sep 2012 15:30

SQL INSERT command is not putting data at the end of the table?

Post by DaveV » Tue 25 Sep 2012 20:02

I started with a table that had 230 entries. My test program used TMSConnection->ExecSQL with this string:

INSERT OEE_Screw_Machine (Date_Time, Machine_ID, Elapsed_Minutes)VALUES (Getdate(), 12345, 2400 )

After running the test program twice, the table contained 232 entries, but the two new entries were at positions 191 and 228 in the table, rather than 231 and 232 as I expected.

My "real" program is again using TMSConnection->ExecSQL with this very long string:

INSERT OEE_Screw_Machine (Date_Time, Machine_ID, Elapsed_Minutes, Run_Minutes, Setup_Minutes, Trblsht_Barloader_Minutes, Trblsht_Machine_Minutes, Tool_Change_Minutes, No_Job_Minutes, No_Stock_Minutes, Planned_Maint_Minutes, Unplanned_Maint_Minutes, Total_Pieces, Scrap_Pieces, Actual_Cycle_Time, Part_Number, Layout_Cycle_Time, DB_Trigger, Stock_Machine, Tool_Adjustment, No_Operator)VALUES( Getdate(), 1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1, 1017, 1018, 1019)

Now the table is 233 entries long (as expected), and the newest insertion shows up at line 192 (right after the first insertion by the test program) when I open the table with Microsoft SQL Server Management Studio, version 10.50.2500.0.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQL INSERT command is not putting data at the end of the table?

Post by AlexP » Wed 26 Sep 2012 12:58

hello,

When selecting from the table without using ORDER BY, the server returns records in the order defined by inner sorting rules of the server itself, but not in the order, in which records were inserted to the table. If you need a definite record order, do this using ORDER BY, e.g. by auto-increment field.

Post Reply