SQL Server add row numbers without ordering

Question

I have a massive data file (tab delimited). I'm experiencing a data shift- at some point, the date field becomes populated with non-date values (the words from the description field to the left of it). As you can imagine, I can't just scroll through the text file, or plop it into excel to dig around- I need to know where to look.

I am trying to add row numbers to the imported table, so that I can identify the row where the data shift occurs. So, I am wondering if there is a way to insert row numbers without having to order by anything (i.e. essentially number the rows of the original data file).

Using

ROW_Number() OVER (ORDER BY (___))

I'm not sure how to accomplish what I'm trying to do, because I can't order on anything- it needs to be numbered exactly as it was imported.

Note that without a sensible ORDER BY, there is no guaranteed order in SQL. E.g. if the server happens to experience a Page Split or any other event that rearranges data 'under the hood', then the order of returned rows will also change. And this may have happened many times before you even run your first query. — Peter B
– Peter B, Commented Oct 26, 2018 at 14:44
To elaborate on the SELECT NULL or SELECT 1 which will not produce repeated results, you'll want to read this: blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/… — S3S
– S3S, Commented Oct 26, 2018 at 14:48
Is it shifting due to a delimiter (tab) in a text column? Hopefully that column is quote wrapped or it is going to be tough to handle. If you are trying to find this line, import entire row into 1 col and then do len(col) - len(replace(col,char(9),"")) — KeithL
– KeithL, Commented Oct 26, 2018 at 15:05
If you could open it in excel, you could add a column and have excel number it for you (just do the first 2-3 rows and then have excel do the rest) - then you will have the exact row order. — Zohar Peled
– Zohar Peled, Commented Oct 26, 2018 at 15:25

Tab Alleman · Accepted Answer · 2018-10-26 14:44:11Z

5

I think your best bet is to import it into a staging table with an IDENTITY column added to it.

answered Oct 26, 2018 at 14:44

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server add row numbers without ordering

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related