creating unique ID on existing table

[TheShabz]

Hi everyone,

I have a table with about 410k records. The goal is to update a column with sequential numbers every 5000 records. So the first 5000 will contain 1, the next 5000 2, etc. Reason for this is because this table needs to be uploaded and due to "system constraints" (yea, my ass) we can only upload 5000 records at a time from a text file. the creation of which isn't important.

Since the "TOP" statement doesnt work with an update query (at least, it hasn't been for me), my plan was to create a field that would be an autonumber field and use that as constraints and run updates as I go. The issue with that is the maxfilelocks entry is maxed out so I cant just create that field.

Anyone know of a better way to accomplish this?

[ajetrumpet]

so you're exporting 5K at a time to .txt

but what's the issue exactly? I guess I didn't catch that. renumbering the records at some point? I read the goal, but it's kind of vague.

[TheShabz]

i have a table with over 400k that needs to be exported 5k at a time into txt. Im creating a temp field that would hold 1, 2, 3, etc so i can export:
SELECT * WHERE temp=1
To make it export 5000 records. I'm having an issue making that temp field to aid in the export.

[ajetrumpet]

so you need a temp field that holds number increments of 1-410,000, right??

in order to identify these recs properly?

[TheShabz]

that would be an intermediate step. If you know of a way to separate them by chunks of 5000, that would be even faster.

[ajetrumpet]

that would be an intermediate step. If you know of a way to separate them by chunks of 5000, that would be even faster.

you can separate these anyway you want, Shabz.

vba code? temp query objects? know what I mean? if you need objects for export only, why not make queries, one at a time and export them each on out to txt files?

you can always break code after any number of loops so the memory stacking doesn't get out of control

[TheShabz]

Nevermind. I had an epiphany. I just recalled an old tool I used called TextCleaver. I'll just export the whole thing into 1 txt file and use that to break them into 5k files.