I have this table called TblCombine
Ldg, Bnk, Combine, Dupe BQ 55.23 BQ55.23 0 BQ 65.23 BQ65.23 0 BQ 75.23 BQ75.23 0 BQ 85.23 BQ85.23 0 BQ 85.23 BQ85.23 0 BQ 85.23 BQ85.23 0 Int 95.23 Int95.23 0 Int 105.23 Int105.23 0 Int 95.23 Int95.23 0 Int 95.23 Int95.23 0
I join the Ldg and Bnk fields into the Combine field.
Ldg, Bnk, Combine, Dupe BQ 55.23 BQ55.23 1 BQ 65.23 BQ65.23 1 BQ 75.23 BQ75.23 1 BQ 85.23 BQ85.23 1 BQ 85.23 BQ85.23 2 BQ 85.23 BQ85.23 3 Int 95.23 Int95.23 1 Int 95.23 Int95.23 2 Int 95.23 Int95.23 3 Int 105.23 Int105.23 1
I have done this in VB where in access as I can follow the process but I am lost at the moment in SQL.
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.
answered Nov 04, 2009 at 03:20 PM
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...
answered May 09, 2010 at 12:30 AM
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
answered Mar 08, 2010 at 12:25 PM
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.
answered Mar 07, 2010 at 01:04 AM
1 LDG varchar 50 1
I have got a little further with my problem.
I use the following SQL to create the data the way I require it. Then I run the the query
DECLARE @Test table (Ldg 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
on Test.Ldg = TblCombine.Ldg where test.Bnk = TblCombine.Bnk
Data after SQl runs,
ldg bnk combine Dupe
BQ 75 BQ75.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.
answered Dec 01, 2009 at 01:50 PM