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 '09 at 03:20 PM
Can you post the table definitions? I have a solution which would be reasonably quick, but it needs either a suitable clustered index, or there to be another column in the table which can uniquely identify each entry.
If you can edit your question to include that, then I will post you a solution.
answered Nov 03 '09 at 08:42 AM
Matt Whitfield ♦♦
A couple of issues you'll need to address:
Similarly, you will be able to get the dupe out by running a select along the lines of:
But this is still going to struggle to be useful without a unique ordering.
answered Nov 03 '09 at 08:44 AM
Melvyn Harbour 1 ♦♦
Folks, I appreciate your help ,Unfortunately I have no control over the data I import, It is from an external source and what I see is what I get.
I'm new to SQL so I'm still in the dark a lot about what I can and cannot do. I just taught that if it could be done in VB there was a chance it could be done in SQL. But I will keep trying. Thank you All
answered Nov 03 '09 at 01:09 PM
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 '09 at 01:50 PM