I am running a DTS Package.
I have a temp table with 1494 records. I am inserting a 'Y' or
'N'into a temp table #HasClaims.
The TempTable name with the Provider Id's(PRPR_ID) is
#TempFACETSNODupes
The @identityID is an identity field counting back from 1494 to 1
I count back from the Max value of the identityid (1494) in the While
loop until I get through all the records. The idea is to check for the
existance of a claims and authorization record and put a 'Y' or 'N'
record in the temptable #HasClaims.
This is running in the Execute SQL Task object of the DTS Package.
The Package runs successfully but only inserts 200 rows into the new
temp table. There should be a row for each provider. Each time it
runs, the number of rows it returns is different. Sometimes it is 205,
then 185, then 210, before it completes the DTS package.
Has anyone run into While looping problems within an Execute SQL task
in a DTS package(SQL 2000)like this
--------------------------------------------------------------
SELECT @identityID = MAX(IDENTITYID) FROM #TempFACETSNODupes
While @identityID >= 1
BEGIN
@PRPRID is the placeholder for the PRPR_ID (Provider)
SELECT @PRPRID = PRPR_ID FROM #TempFACETSNODupes WHERE IDENTITYID =
@identityID
IF exists( SELECT CLCL_ID FROM dbo.CMC_CLCL_CLAIM CLCL WHERE
CLCL.PRPR_ID = @PRPRID)
BEGIN
INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)
VALUES( @PRPRID, 'Y', @identityID)
END
ELSE INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)
VALUES( @PRPRID, 'N', @identityID)
------------------------------------------------------------
SELECT @identityID = @identityID - 1
END