On Jan 31, 5:03*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Hi Erland and Bob,

Thanks both a bundle for your responses. Looks like there is life on
planet
Google Groups after all, much appreciated!

The specifics is that we are doing email deployments but google is
moving
all of the email sent to gmail users to their spam boxes. As a result,
we have
to "chunk" the gmail users out of the total amount and send in
manageable
batches. We have figured that 80,000 per batch out of the total gmail
users
in the table is possible.

And, Erland, to answer your question, I would prefer to turn this into
a stored
procedure we can use for the purpose of "chunking" email addresses.
Accordingly,
the number per batch should be a variable parameter.

Bob, the table we are querying against is pretty simple. Essentially,
it has one
one column - "email_address" which is a varchar. Its data is about 1
million email
addresses (but that number changes often). The result set table(s)
should only have
two columns, the count (INT) and the email_address (varchar). Please
see below.

The query I am trying to write is supposed to dump each result set
(batch)
to a text file in some folder on the hard drive. Each result set
should have
a count and the email addresses as columns. Something like this:

RESULT 1:

[COUNT] [EMAIL_ADDRESS]
1 name (AT) gmail (DOT) com
2 name (AT) gmail (DOT) com
3 name (AT) gmail (DOT) com
4 name (AT) gmail (DOT) com
5 name (AT) gamil (DOT) com
....
80,000 name (AT) gmail (DOT) com

RESULT 2:

[COUNT] [EMAIL_ADDRESS]
80,001 name (AT) gmail (DOT) com
80,002 name (AT) gmail (DOT) com
80,003 name (AT) gmail (DOT) com
80,004 name (AT) gmail (DOT) com
80,005 name (AT) gamil (DOT) com
....
16,000 name (AT) gmail (DOT) com

And so on up to the to total amount of the gmail addresses out of the
original table.

So, the statement should read something like this (pseudo code):

select all distinct users
from the master table
where email_address like '%gmail.com'
return in batches of N (such as 80,000)
and write each batch to a text file on the
hard drive.

I hope I have explained myself well. Let me know if anything is
unclear.

Thanks a bundle for your help.

pbd22 wrote:
You keep making a point of saying "distinct". Does that imply that there are
duplicate email addresses in that 1-million row table?


I assume you can handle this part, correct? It's only batching the data that
you need help with?

I can think of a couple approaches. Here's one:
1. Create a temp table (#batches) with an identity column (indentcol) and an
email column (email). Insert the distinct email addresses into it:
insert #batches (email)
select distinct email_address from master_table where email_address like
'%gmail.com'

Then use a WHILE loop to retrieve the batches, using a variable to keep
track of them.
declare @batchsize int --convert this to a parameter for your sproc
set @batchsize=80000

declare @lastrec int, @endrec int
set @lastrec=(select max(identcol) from #batches)
set @endrec=@batchsize
WHILE @endrec-@batchsize<=@lastrec
BEGIN
select email from #batches where identcol >=@lastrec
--process the batch
delete #batches where identcol >=@lastrec
set @endrec=@endrec + @batchsize
END

pbd22 (dushkin (AT) gmail (DOT) com) writes:
The simple-minded solution would be:

WITH numbered AS (
SELECT email, row_number() OVER(ORDER BY email) AS rowno
FROM addresses
WHERE email LIKE '%@gmail.com'
)
SELECT email
FROM numbered
WHERE rowno > (@batchno - 1) * @batchsize
AND rowno <= (@batchno - 1) * @batchsize

But it would be far more efficient to do:

CREATE TABLE gmail_addresses (rowno int NOT NULL,
gmail nvarchar(255) NOT NULL,
CONSTRAINT pk_gmail PRIMARY KEY CLUSTERED (rowno),
CONSTRAINT pk_unique UNIQUE NONCLUSTERED(gmail))

INSERT gmail_addresses(rowno, gmail)
SELECT row_number() OVER(ORDER BY (SELECT 1)), gmail
FROM (SELECT DISTINCT email
FROM addresses
WHERE email like '%@gmail.com') AS x

Then you have materialised the row number once for all, and a selection
of 80000 accounts will be quick.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx