Query and Export from SQL Server Unsorted Table

Question

First I apologize for my bad english, not my language.

My problem: I have a table with about 10 million bank transaction records. It has no PK and does not sort like any column.

My job is to create a page for filtering and exporting to csv. But the line limit for Csv export is about 200k records.

I have an idea like:

create 800 tables from 800 ATMs (just an idea, I know this is stupid) and send data from the main table to it 1 time per day => export to 800 csv files
use Linq to get 100,000 records at a time and then next time, I'll skip them. But I get stuck when I Skip

need a team OrderBy

and I got OutOfMemoryException

with it
```
db.tblEJTransactions.OrderBy(u => u.Id).Take(100000).ToList()

      

        
        
        
      

    
```

Can anyone help me, every idea is appreciated (my boss said I can use anything, including creating one hundred tables, using Nosql ...)

+3

HiepPD 05 Apr 17 at 2:52

2 answers

Vladimir Baranov · Answer 1 · 2017-04-05T04:46:19+0000

If you don't have a primary key in the table, add it. The easiest and easiest way is to add a column int IDENTITY

.

ALTER TABLE dbo.T 
ADD ID int NOT NULL IDENTITY (1, 1)

ALTER TABLE dbo.T 
ADD CONSTRAINT PK_T PRIMARY KEY CLUSTERED (ID)

If you are unable to modify the original table, make a copy.

Once the table has a primary key, you can sort it and select 200k row chunks / pages with predictable results.

Tomato32 · Answer 2 · 2017-04-05T04:03:35+0000

I am not sure about my solution. But you can go and try:

select top 1000000 *, row_number() over (order by (select null)) from tblEJTransactions

The above query returns a sorted list.

And then you can use Linq to get the result.