Split data in SQL Table

Question

Post reply

Split data in SQL Table

Andrew Collins

Ten Centuries

Points: 1369
More actions
August 23, 2005 at 8:34 am

#164870

I have a table of 500,000 records and a customer wants to receive the data in seperate excel sheets of 10,000 records in each. Is there an esy way of doing this.
The table contains a unique reference and a fax number, they ideally want to have the filename for each the same as the first record in the table.
Even if i can split the data into mutiple tables i could export manually.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

MidwestDBA SSC Veteran Points: 299 More actions · Answer 1

I am not sure the fax number will help you, but what kind of unique reference are you using?

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 2

the unique refence is a refence generated when the record is added to the file, it is not in any particular sequence

forgot to mention they want the data sorted by fax number.

i wouldnt be a problem adding another sequenced reference number.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

If it wouldn't be a problem to add another sequenced refernece number then it would be simple like

SELECT * FROM TableName WHERE NewSequence BETWEEN 0 to 10000

SELECT * FROM TableName WHERE NewSequence BETWEEN 10000 to 20000 etc

Or

create an application (Ex. VB) with server side static forward only record set and put them in different files.

Regards,
gova

MidwestDBA SSC Veteran Points: 299 More actions · Answer 4

Adding another sequenced number would probably make things more complex.

Given the information I have, I would think you would have to count these records as you go through the resultset and when you hit 10K, switch to a new output file. Very messy, I know, but without being able to mathmatically pull 10K records at a time, you may be forced with that solution.

MidwestDBA SSC Veteran Points: 299 More actions · Answer 5

Wouldn't it be a problem using those sequence numbers if output was in fax number order? What happens when new fax number is added?

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 6

there wouldnt be anymore fax numbers added to this data, this is a dataset which is final and to be given to a customer as is, they just want it delivered in files of 10,000 records rather than 500,000

MidwestDBA SSC Veteran Points: 299 More actions · Answer 7

Then I would use govinn's solution. I believe you would have to apply a clustered index to the fax number to order the data the way you need it when you implement the counter.

Japie Botma SSCrazy Points: 2990 More actions · Answer 8

To summarize:

Create a Temp table with an autokey and the other fields you want to output. Then run : INSERT INTO TempTable SELECT Field, Field ... FROM MainTable ORDER BY FaxNo.

Now use govinn's solution to get your resultsets.

5ilverFox
Consulting DBA / Developer
South Africa

Andrew Collins Ten Centuries Points: 1369 More actions · Answer 9

Thank you for all the replies, I have now done what I needed to do