Loop through a table to update Records using SQL 2000

The answer would probably be to stage the import into a temporary table with an identity column, calculate/update the dupe count, and then migrate to your final table.

If this intermediate processing is acceptable then Melvyn or Matt would probably have the queries you need ready to present to you based on their comments.

Oh, sorry... I'm not sure how I lost track of this one so very badly.

Pator, here's the SQL Server 2000 method I was thinking off. The clustered index, TABLOCKX, and MAXDOP thingies are absolutely essential...

--===== Create and populate a test table.
     -- This is NOT a part of the solution
 CREATE TABLE #TblCombine
        (
        Ldg     VARCHAR(5),
        Bnk     DECIMAL(9,2),
        Combine VARCHAR(20),
        Dupe    INT
        )

 INSERT INTO #TblCombine
        (Ldg,Bnk,Combine,Dupe)
 SELECT 'BQ','55.23','BQ55.23','0' UNION ALL
 SELECT 'BQ','65.23','BQ65.23','0' UNION ALL
 SELECT 'BQ','75.23','BQ75.23','0' UNION ALL
 SELECT 'BQ','85.23','BQ85.23','0' UNION ALL
 SELECT 'BQ','85.23','BQ85.23','0' UNION ALL
 SELECT 'BQ','85.23','BQ85.23','0' UNION ALL
 SELECT 'Int','95.23','Int95.23','0' UNION ALL
 SELECT 'Int','105.23','Int105.23','0' UNION ALL
 SELECT 'Int','95.23','Int95.23','0' UNION ALL
 SELECT 'Int','95.23','Int95.23','0'

--===================================================================
--      Solution starts here
--===================================================================
--===== If you don't have THIS index on the table, then it won't work
 CREATE CLUSTERED INDEX IX_TblCombine_CoverUpdate
     ON #TblCombine (Combine)

--===== Create a couple of necessary obviously named variables
DECLARE @PrevDupe    INT,
        @PrevCombine VARCHAR(20)

--===== Update the dupe column
 UPDATE #TblCombine
    SET @PrevDupe    = Dupe = CASE WHEN Combine = @PrevCombine THEN @PrevDupe+1 ELSE 1  END,
        @PrevCombine = Combine
   FROM #TblCombine WITH(TABLOCKX) --Absolutely essential.  DO NOT REMOVE
 OPTION (MAXDOP 1) --Absolutely essential.  DO NOT REMOVE

--===== Show the result
 SELECT * FROM #TblCombine ORDER BY Combine,Dupe

Jeff I would be interested in a SQl solution, I did get around the problem by exporting the sql query as a text file and then re-import into the database. I know it's not a perfect solution but it got the job done. However a SQl solution would still be very benificial. Regards & Thanks Pator

Pator,

I realize this is a pretty old post... do you still need an answer on this because, if you do, I have one. I just don't want to take the time to write it up if you don't need it.

Matt ,
Thanks for the mail. this is my table,

1 LDG varchar 50 1
0 BNK decimal 9 1
0 COMBINE varchar 50 1
0 DUPE numeric 9 1
0 UniqueID varchar 50 1
0 id int 4 1

I have got a little further with my problem.
The ID field in table is not used and can be changed to any field type if required.

I use the following SQL to create the data the way I require it. Then I run the the query
to file and use DTS to import it into a temp table and use sql to transfer the required data into TBlCombine. I know it's not a perfect answer but it's gets the job done. But it would be better just to run an update process of some sort. I don't have the skill as yet to modify this SQl to change it to an update process.

DECLARE @Test table (Ldg varCHAR(50),

Bnk decimal(9,2),

Combine varchar(50),

Dupe int IDENTITY(1,1))

INSERT INTO @Test (Ldg, Bnk, Combine)

SELECT Ldg, Bnk, Ldg+CAST(Bnk as varchar(25))

FROM TblCombine ORDER BY TblCombine.LDG , TblCombine.BNK , TblCombine.COMBINE

SELECT rtrim(TblCombine.LDG) as Ldg, TblCombine.BNK, rtrim(TblCombine.LDG) + rtrim(cast(TblCombine.BNK as varchar(50))) as Combine , test.Dupe - TblCombine.Dupe + 1 AS Dupe

FROM @Test test

INNER JOIN (SELECT Ldg, Bnk, MIN(Dupe) AS Dupe

        FROM @Test
        GROUP BY Ldg, Bnk)Tblcombine

on Test.Ldg = TblCombine.Ldg where test.Bnk = TblCombine.Bnk

Data after SQl runs,

ldg bnk combine Dupe

BQ 75 BQ75.00 1
BQ 85 BQ85.00 1
BQ 85 BQ85.00 2
BQ 85 BQ85.00 3
BQ 95 BQ95.00 1
INT 95 INT95.00 1
INT 95 INT95.00 2
INT 95 INT95.00 3
INT 105 INT105.00 1
INT 105 INT105.00 2
INT 106 INT106.00 1
INT 107 INT107.00 1

AS you can see the number in the Dupe field is based on the combine field, which increases by 1 each time the field is the same.

Any further help would be great.

Regards

Pator