0

I have two different database, I need move away from one DB so I replicate same scenario where table should be loaded from INSERT INTO SELECT * statement.

However, when I do the same thing for new DB the table has different ordering from the original one.

e.g

DB1 - tbl_temp DB2 - tbl_temp

INSERT INTO DB1.tbl_temp
col1,
col2,
    SELECT col1, col2 
    FROM DB3.tbl_source

Result is: when I use SELECT * FROM DB1.tbl_temp

col1 col2
abc 123
def 456 
INSERT INTO DB2.tbl_temp
col1,
col2,
    SELECT col1, col2 FROM DB3.tbl_source

Result is: when I use SELECT * FROM DB2.tbl_temp

col1 col2
def 456
abc 123

Both has same source but result has different order, is there any configuration needed to fix the issue ? Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
str028
  • 186
  • 10
  • 2
    If you want a specific order, do `ORDER BY`. – jarlh Jan 07 '22 at 09:59
  • 3
    The order of the rows is never guaranteed. Tables rows don't follow any order per se. – Jayvee Jan 07 '22 at 09:59
  • I mean in my example there must be something between DB right ? – str028 Jan 07 '22 at 10:04
  • both table uses same sql statement and same source, but how does the result order is different ? – str028 Jan 07 '22 at 10:05
  • 1
    Because the order you get is not guaranteed and will depend on the execution plan and in some cases what physical page the rows happen to be stored on – Martin Smith Jan 07 '22 at 10:17
  • @MartinSmith So you mean this is randomly generated ? I tried doing same steps again in DB1 and DB2 with new table but the order still persists – str028 Jan 07 '22 at 10:44
  • No - I never said it was random. It is not guaranteed and will depend on the execution plan. SQL Server isn't going to go out of its way to randomise it. If the returned order is important to you you need an `ORDER BY`. It is that simple – Martin Smith Jan 07 '22 at 10:45
  • Okay thanks will leave this mystery here. btw my last resort is to use ORDER BY – str028 Jan 07 '22 at 10:50
  • 1
    well that is the **only** way to get a guaranteed result so it should be your first resort – Martin Smith Jan 07 '22 at 10:51

0 Answers0