question

aRookieBIdev avatar image
aRookieBIdev asked

The order of records in a select query

HI , I have 2 tables Table and Table_1 which are my destination table in my data upload. They have the same columns and same structure. I am executing two select queries on the two tables which does a LEFT JOIN WITH THE same SOURCE TABLE TO FIND THE NEW RECORDS only and then i insert them seperately. However the select query brings me the same data for both the tables but in different order. The only difference between the select queries is that one has with (nolock) and other doesnt. Is it because of the with (nolock) or could it be because of anything else like indexing. Thanks in advance, Kannan
order-bynolock
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Without an `order by` clause, the order of any select cannot be guaranteed, predicted, or relied upon. Yes the table hints and indexing can affect the order as can load on the machine at the time the query runs, impact of other transactions, amount of RAM, and a whole host of environment/hardware/software settings. But the bottom line is without an `order by`, the data is simply not ordered.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mpradeep_21 avatar image
mpradeep_21 answered
,You can use the merge statement as below MERGE Table_1 AS target USING Table AS source ON (target.[ColumnName1] = source.[ColumnName1]) WHEN MATCHED THEN UPDATE SET [ColumnName2] = source.[ColumnName2] WHEN NOT MATCHED THEN INSERT ([ColumnName1], [ColumnName2]) VALUES (source.[ColumnName1], source.[ColumnName2]) Hope this helps
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.