May 21, 2008 at 3:18 pm
Hi. I have a query to write that I just cant get my head around the best way to do it in order that it has some chance of working efficiently. What follows is a much simplified explanation of my problem but it should give you all the general idea.
I have two tables. The data in tableA is a list of codes and associated descriptions and is periodically cleared and re-imported from a new source. The data in tableB is a master code and description lookup table. When tableA is populated with new data, I need to compare the codes to those already in tableB (easy enough) and where there is a match on the code but the description is different, I need to map the codes in tableA to new values prior to inserting the records into tableB. This is done by adding a prefix to the codes that indicates the source of the import and makes them unique in the majority of cases.
However, and this is where my problem lies, in some cases adding the prefix will mean the length of the new code exceeds the 10 character maximum size allowed in the table. In this case, I have been instructed to take the first 6 characters of the code and then add 0001 to the end, check if this code would be unique in tableB, if not add 0002 instead and so on until I find a unique value for the new code. It is how to do this last bit (find the unique numeric suffix to add) efficiently that is blowing my mind today.
The two tables will not contain huge numbers of records - usually only a couple of hundred each, maximum envisaged is no more than 10,000 each.
CREATE TABLE [dbo].[tableA](
[Code] [nvarchar](50) NOT NULL,
[Description] [nvarchar](max) NULL
) ON [PRIMARY]
Go
CREATE TABLE [dbo].[tableB](
[Code] [nvarchar](50) NOT NULL,
[Description] [nvarchar](max) NULL
) ON [PRIMARY]
Go
Insert into dbo.tableA Values ('AAAAAAAAAA', 'Description 1')
Insert into dbo.tableA Values ('BBBBBBBBBB', 'Description 2')
Insert into dbo.tableB Values ('AAAAAAAAAA', 'Current Description 1')
Insert into dbo.tableB Values ('AAAAAA0001', 'Current Description 1 - 0001')
Insert into dbo.tableB Values ('AAAAAA0002', 'Current Description 1 - 0002')
Insert into dbo.tableB Values ('AAAAAA0003', 'Current Description 1 - 0003')
go
With the above data, when I do the checks outlined above for the record in tableA with a code of 'AAAAAAAAAA', I should find that the code already exists in tableB and that if I add the prefix to it then it will be longer than the allowed 10 characters. Up to here is no problem. I should then go down the route of trying the first 6 characters combined with a 4 digit numeric suffix until I find a unique value, resulting in me trying 'AAAAAA0001', find this exists in tableB already, try 'AAAAAA0002', again find it exists and so on until I get to 'AAAAAA004' which I should find is unique and so that should be the value chosen to insert into tableB with the associated desciption. I hope that makes sense.
Thanks for your patience in reading this and thanks in advance for any advice.
May 22, 2008 at 3:48 am
Hi,
Hope this query will help you,Awaiting your Response
Declare @Value nvarchar(10)
set @Value = (select substring (max(code),7,4)+1 from tableB where code like (select substring (code,1,6)+'%' from Tablea where Code Like 'a%') and code not like 'aaaaaaaaaa')
select @Value
insert into TableB
select substring(code,1,6)+'000'+@Value,'current ' + description + ' - 000'+@Value from TableA where code like 'a%'
select * from TableB
Rajesh
May 22, 2008 at 9:30 am
Rajesh
Thanks for your suggested query. I was able to use it to help me get a working solution although I then ended up changing tack slightly and using a conversion table to hold the before and after mapping codes for audit reporting purposes later on in the full process. This means instead of inserting records into TableB, I was updating a new field in TableA and then the insert into TableB from there is easy.
DECLARE @MaxVals TABLE (Stub_Code nvarchar(50) NOT NULL,Next_Value INT)
Insert into @MaxVals
Select substring(code,1,6) as Stub_Code, max(substring(code,7,4))as MaxVal from TableA
where (isnumeric(substring(code,7,4))=1)
group by substring(code,1,6)
Update TableA Set Mapped_Code = substring(TableA.code,1,6)+ right('0000'+cast((isnull(next_value,0) + 1) as varchar(4)),4)
From TableA inner join TableB ON TableA.Code = TableB.Code
left outer join @MaxVals on substring(TableA.code,1,6)= stub_code
Where ((len(@TCode + TableA.HRG_Code)>50) and (TableA.Description <> TableB.Description))
This seems to be reasonably fast - fractions of a second on a 7000 row test table.
Many thanks for you help.
May 22, 2008 at 1:40 pm
Be careful with your use of the ISNUMERIC function. It does not always behave like you may expect it to. You may want to consider using PATINDEX instead.
http://www.sqlservercentral.com/articles/SQL+Puzzles/practicalusesofpatindex/2236/
May 23, 2008 at 3:06 am
John
Thanks for the advice. I will be looking into it.
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply