Query to Count and copy records to new table

[May Q. via AccessMonster.com]

I have a table of 600,000 records. I need to export 100,000 records at a
time to an excel delimted : file. So that I will have 6 files. How do I
create a query to count and then copy records to a temp table of 100,000
records? How do I tell it to pick up the next set of 100,000 records??
Please Help...

Thanks,
May

 

[Tom Ellison]

Dear May:

For the first 100000 rows:

SELECT TOP 100000 *
FROM YourTable
ORDER BY SomeColumn(s)

For the second 100000:

SELECT TOP 100000 * FROM (
SELECT TOP 500000 *
FROM YourTable
ORDER BY SomeColumn(s))
ORDER BY SomeColumn(s) DESC

You need to supply the correct table name and columns by which to sort. The
sorting needs to be unique. From the time you run the first query till you
finish the last one, the table must be unchanging. Don't allow users to
edit, add, or delete rows.

I assume the figure of 600000 rows is approximate. You'll need to adjust
the values in the query accordingly.

Tom Ellison

 

[May Q. via AccessMonster.com]

Okay, I have the following to insert the 100,000 into a table. But how do I
tell it to insert the second set of 100,000???

Thanks.
May
INSERT INTO File1
SELECT TOP 100000 *
FROM FileAA;

Dear May:

For the first 100000 rows:

SELECT TOP 100000 *
FROM YourTable
ORDER BY SomeColumn(s)

For the second 100000:

SELECT TOP 100000 * FROM (
SELECT TOP 500000 *
FROM YourTable
ORDER BY SomeColumn(s))
ORDER BY SomeColumn(s) DESC

You need to supply the correct table name and columns by which to sort. The
sorting needs to be unique. From the time you run the first query till you
finish the last one, the table must be unchanging. Don't allow users to
edit, add, or delete rows.

I assume the figure of 600000 rows is approximate. You'll need to adjust
the values in the query accordingly.

Tom Ellison

I have a table of 600,000 records. I need to export 100,000 records at a
time to an excel delimted : file. So that I will have 6 files. How do I

[quoted text clipped - 4 lines]

Thanks,
May

 

[Tom Ellison]

Dear May:

If you'll look carefully, you should see I already answered that in my
previous reply. I recommend you also read and make sure you understand the
details I described for how this must be done.

Tom Ellison

Okay, I have the following to insert the 100,000 into a table. But how do
I
tell it to insert the second set of 100,000???

Thanks.
May
INSERT INTO File1
SELECT TOP 100000 *
FROM FileAA;

Dear May:

For the first 100000 rows:

SELECT TOP 100000 *
FROM YourTable
ORDER BY SomeColumn(s)

For the second 100000:

SELECT TOP 100000 * FROM (
SELECT TOP 500000 *
FROM YourTable
ORDER BY SomeColumn(s))
ORDER BY SomeColumn(s) DESC

You need to supply the correct table name and columns by which to sort.
The
sorting needs to be unique. From the time you run the first query till
you
finish the last one, the table must be unchanging. Don't allow users to
edit, add, or delete rows.

I assume the figure of 600000 rows is approximate. You'll need to adjust
the values in the query accordingly.

Tom Ellison

I have a table of 600,000 records. I need to export 100,000 records at a
time to an excel delimted : file. So that I will have 6 files. How do
I

[quoted text clipped - 4 lines]

Thanks,
May

 

[John Spencer]

Just a quick note about the limits in Excel
Worksheet size 65,536 rows by 256 columns

So 100,000 rows is not going to make it into one Excel worksheet.

 

[Tom Ellison]

Dear John,

Good point. I had completely ignored that side of the problem.

Tom Ellison

 

[May Q. via AccessMonster.com]

okay, I admit that I don't get it. Is there a way that after my insert of
100000 records that I can delete the top 100000 from the main table and then
I can run my query 5 0r 6 times?

thanks,
May

Dear May:

If you'll look carefully, you should see I already answered that in my
previous reply. I recommend you also read and make sure you understand the
details I described for how this must be done.

Tom Ellison

Okay, I have the following to insert the 100,000 into a table. But how do
I

[quoted text clipped - 40 lines]

 

[May Q. via AccessMonster.com]

How about text delimited file, delimited by :colon??

Thanks,
May

Just a quick note about the limits in Excel
Worksheet size 65,536 rows by 256 columns

So 100,000 rows is not going to make it into one Excel worksheet.

Dear May:

[quoted text clipped - 30 lines]

 

[May Q. via AccessMonster.com]

Hi Tom, Could you explain your example to me. I don't understand how it will
copy each set.

Thanks,
May

Dear May:

If you'll look carefully, you should see I already answered that in my
previous reply. I recommend you also read and make sure you understand the
details I described for how this must be done.

Tom Ellison

Okay, I have the following to insert the 100,000 into a table. But how do
I

[quoted text clipped - 40 lines]

 

[Tom Ellison]

Dear May:

My examples were just to select the sets, not to copy them. Changing a
SELECT query into an append or make table is pretty easy.

Let's look at the code. I wrote before, "For the second 100000:

SELECT TOP 100000 * FROM (
SELECT TOP 500000 *
FROM YourTable
ORDER BY SomeColumn(s))
ORDER BY SomeColumn(s) DESC

End quote.

The inner query says:

SELECT TOP 500000 *
FROM YourTable
ORDER BY SomeColumn(s)

I reversed the place where the DESC should have been. Please make that:

SELECT TOP 500000 *
FROM YourTable
ORDER BY SomeColumn(s)) DESC

Sorry! Now, this selects rows from 100001 to 600000. Then:

SELECT TOP 100000 * FROM (
SELECT TOP 500000 *
FROM YourTable
ORDER BY SomeColumn(s) DESC)
ORDER BY SomeColumn(s)

This selects the first 100000 rows from the last 500000 rows. Is that
better now?

Do remember that the exact number of rows in the table is essential to
making this work correctly. If there are realy 599,999 rows in the table,
then the second 100000 rows would be:

SELECT TOP 100000 * FROM (
SELECT TOP 499999 *
FROM YourTable
ORDER BY SomeColumn(s) DESC)
ORDER BY SomeColumn(s)

Tom Ellison

Hi Tom, Could you explain your example to me. I don't understand how it
will
copy each set.

Thanks,
May

Dear May:

If you'll look carefully, you should see I already answered that in my
previous reply. I recommend you also read and make sure you understand
the
details I described for how this must be done.

Tom Ellison

Okay, I have the following to insert the 100,000 into a table. But how
do
I

[quoted text clipped - 40 lines]

Thanks,
May

 

[John Spencer]

No problem putting that many records into any text file - no matter what
method you use to delimit it. I just noted that you had said " I need to
export 100,000 records at a time to an excel delimted : file."

As I noted EXCEL will not handle that many rows.

How about text delimited file, delimited by :colon??

Thanks,
May

Just a quick note about the limits in Excel
Worksheet size 65,536 rows by 256 columns

So 100,000 rows is not going to make it into one Excel worksheet.

Dear May:

[quoted text clipped - 30 lines]

Thanks,
May