October 21, 2007 at 6:42 am
I am new to T-SQL and I have a problem that I cannot fathom a solution to.
I have TBL_Address that has (amongst other columns) ‘ID’ and ‘KEY’. The ID’s are globally unique, but the key is unique to an address. There maybe (and are!) duplicate addresses in the table, meaning that the key is repeated in the table.
An example of TBL_Address would be:
*ID* *KEY*
1 10
2 20
3 30
4 30
5 40
6 40
7 40
As you can see there are 7 address records, but there are repeats. I have been given a script that will merge records on the system by the developers, that reads an AddressMap table. The AddressMap uses two columns Master and Child, where the Child record is merged into the Master.
What I need to do is populate the AddressMap table for the merge script, but in such a way as to reduce the load on the system for the merge. That means for identical addresses where there are more than two entries using the same master ID. For addresses where there are just two entries using the first encounter as the master ID and the second as the child ID.
For the above example the AddressMap table should look like this:
*MASTER ID* *CHILD ID*
3 4 -- Merge 4 into 3
5 6 -- Merge 6 into 5
5 7 -- Merge 7 into 5
What I am looking for is some code that will enable me to do this:
INSERT INTO AddressMap (Master, Child)
SELECT ……
Any help would be appreciated.
Kind regards,
Phil
Regards,
Phil
October 21, 2007 at 6:55 am
Phil,
Check this snippet.....
IF OBJECT_ID( 'tempdb..#TBL_Address' ) IS NOT NULL
DROP TABLE #TBL_Address
CREATE TABLE #TBL_Address
(
[ID] INT NOT NULL PRIMARY KEY CLUSTERED,
INT NOT NULL
)
INSERT #TBL_Address( ID, )
SELECT 1, 10
UNION ALL
SELECT 2, 20
UNION ALL
SELECT 3, 30
UNION ALL
SELECT 4, 30
UNION ALL
SELECT 5, 40
UNION ALL
SELECT 6, 40
UNION ALL
SELECT 7, 40
SELECTT2.[ID] AS [MASTER ID], T1.[ID] AS [CHILD ID]
FROM#TBL_Address T1
INNER JOIN
(
SELECT, MIN( [ID] ) AS [ID]
FROM#TBL_Address A
GROUP BY
HAVING COUNT( [ID] ) > 1
) T2 ON T1. = T2. AND T1.[ID] != T2.[ID]
IF OBJECT_ID( 'tempdb..#TBL_Address' ) IS NOT NULL
DROP TABLE #TBL_Address
--Ramesh
October 22, 2007 at 1:49 am
Thanks Ramesh, that was exactly what I was looking for!
Thanks once again,
Phil
Regards,
Phil
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply