May 11, 2009 at 11:26 am
I have a client whos database table uses a multicolumn PK. Unfortunately, the columns used for the key allowed for some duplicate entries. The key was on the organization, individual and course. Each individual was also assigned a unique id (Studentid). Unfortunately, when the individuals changes organizations there were multiple records created within the data warehouse for each individual that had attended courses.
My job is to clean up the database.
I am thinking of using the following, but can not seem to get the syntax correct.
@Primary and @ Secondary will be assigned when the Stored Procedure is called.
@Primary is the target StudentID ( the one we want to keep)
@secondary is the studentid we want to drop after we merge the records together.
Declare @HR Varchar(3) -- used to store the value based on primary or secondary records.
There are about 50 fields that we need to combine based on the following logic. If the primary record has a value, leave it alone. If the primary record is blank take the data from the secondary record and merge it into the primary record. Pls Note: We are not using SQL 2008 so the new Merge function will not work.
Case
when (Select [HOME ROOM] from tbl_name where [student id] = @primary)
then Set @HR = (Select [HOME ROOM] from tbl_name where [student id] = @primary )
else Set @HR = (Select [HOME ROOM] from tbl_name where [student id] = @Secondary)
End Case
The idea was to check to see if a value existed in the primary record. If so, assign it to @HR. If not assign whatever value exist in the secondary record.
Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
May 11, 2009 at 12:50 pm
Hi Ray
I'm not sure if I understood you. But if you want to update the fields of your @Primary only if they are currently NULL try this:
[font="Courier New"]DECLARE @t TABLE (Id INT, C1 VARCHAR(100), C2 VARCHAR(100))
INSERT INTO @t
SELECT 1, 'foo', 'world'
UNION ALL SELECT 2, 'hello', NULL
DECLARE @Primary INT
DECLARE @Secondary INT
SELECT
@Primary = 2,
@Secondary = 1
UPDATE t1 SET
C1 = ISNULL(t1.C1, t2.C1),
C2 = ISNULL(t1.C2, t2.C2)
FROM @t t1
CROSS JOIN (SELECT * FROM @t WHERE Id = @Secondary) t2
WHERE t1.Id = @Primary
DELETE FROM @t WHERE Id = @Secondary
SELECT * FROM @t
[/font]
Greets
Flo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply