You need to add some intelligence to the query to tell it which rows (rather than the same rows) to pick. One way to approach this is to
a) put all the data you want to export into temporary table
b) add a column to indicate a serial number
c) pick the rows to export based on the serial number.
In your case, the code would be something like this:-- This puts all the data into a temporary table.
-- Fix your query - what should be E.Name? I set it to "Something?"
SELECT
DISTINCT M.POLICY_NUMBER,
M.CLIENT_NUMBER,
C.FNAME1,
C.LNAME1,
E3.DESCRIPTION AS EXPOSURE_TYPE
INTO
#tmp
FROM
MPL_EXPOSURE M
INNER JOIN EDIT_LONG_CODE E
ON E.TBNAME = 'MPL_EXPOSURE' AND E.NAME = 'Something?'
WHERE
M.POLICY_NUMBER NOT LIKE 'Q%'
-- Now add a sequence number column to the temp table
ALTER TABLE #tmp ADD SeqNum INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED;
And then run the select statement from the temp table.DECLARE @ChunkSize INT, @TotalRowsToExport INT;
SET @ChunkSize = 10;
SELECT @TotalRowsToExport = COUNT(*) FROM #tmp;
DECLARE @counter INT
SET @counter = 0;
WHILE @counter < @TotalRowsToExport
BEGIN
SELECT TOP (@ChunkSize)
POLICY_NUMBER,CLIENT_NUMBER,FNAME1,LNAME1,EXPOSURE_TYPE
FROM
#tmp
WHERE
SeqNum > @counter
ORDER BY
SeqNum;
SET @counter = @counter+@ChunkSize;
END
The code compiles, but I have not tested it.
Maximum number of records
Posted - 07/20/2012 : 09:39:50
