0

I have data from a 3rd party system in excel. when I import the data to SQL server 2016 table the data is rearranged and I can't work on it since the data is grouped

was trying to change it from batch processing to row processing

No Error Messages... The results are the data is shuffled when comparing it to the excel file

Paul
  • 1
  • 3
    "the data is shuffled when comparing it to the excel file" - there is no inherent order of data in a table. If you want to preserve order then add a row number column to the original source then you can order by that when you select to read it back in the same order – Martin Smith Oct 14 '19 at 10:20
  • What do you mean by "shuffled"? The rows aren't in the same order as in Excel? Rows in SQL Server tables aren't stored in any default order. You need to specify ORDER BY when you read them. – Greg Low Oct 14 '19 at 10:21
  • Does your Excel file have a column you can get the correct order by using an `ORDER BY`? If not, then there is no way you can retain that order with the data in the Excel File; you need to have a way to replicate the order from the file by using an `ORDER BY` clause. That might mean editing the file to include a "row number", like Martin said. – Larnu Oct 14 '19 at 10:22
  • when I run the same document on SQL server 2014 the data isnt shuffled – Paul Oct 14 '19 at 10:39
  • 1
    *"when I run the same document on SQL server 2014 the data isnt shuffled"* coincidence. nothing more, nothing less. A query without an `ORDER BY` clause has no guaranteed order. You should never rely on the "chance" of the order returned being the "insertion order". Unless you have a way to get the order by an `ORDER BY` that information is immediate lost at the point of the `INSERT`. – Larnu Oct 14 '19 at 11:18
  • Data is stored in Groups Group1 1 2 3 4 Group2 1 2 3 4 the data has the above structure when i import the data they get shuffled. some of group 1 data will end up in group 2 and vise versa and i cannot index the data since it cant be manipulated. when i use the MSSQL 2014 the data retains the same structure. The server we are now using is MSSQL 2016 – Paul Oct 14 '19 at 12:15
  • You are mistaken in multiple ways. Firstly, you CAN index a table - an index does not prevent "manipulation". Secondly, you most likely assume that rows in a table have a particular order that can be relied on when selecting without the user of an order by clause. If you want a resultset that has ordered rows, you must supply an order by clause to generate it. Perhaps your real question is how to import and save rows in a particular order when the data itself has no columns that can be used to support that order? – SMor Oct 14 '19 at 13:02
  • @SMor _Perhaps your real question is how to import and save rows in a particular order when the data itself has no columns that can be used to support that order_ How would you go about this? – Paul Oct 14 '19 at 13:50
  • One such discussion is [preserving order](https://stackoverflow.com/questions/14424929/preserving-order-by-in-select-into). – SMor Oct 14 '19 at 15:51

0 Answers0