sorry for the dealyed response...
Table 1 :
[person_names]
--------
some_id
last_name
first_name
mid_name
name_eff_dt
---------
Table 2:
[person_demographics]
---------
ID
last_name
first_name
mid_name
sex
dob
---------
Let us try to Update last_name,first_name,mid_name in [person_demographics] with the value in [person_names].
Every time any part of the name changes. A new record with an effective date is entered in [person_names]
To have the latest last_name in [person_demographics] we have to Update the last_name, first_name and mid_name.
Let us do that by using DTS.
STEP 1:
Create TWO new connections.
STEP 2: FROM the TASK menu Select "Data Driven Query Task"
STEP 3: Right-click on the selected task and SELECT properties.
STEP 4:
a) Select the appropraite Connection.(The connection to the Database where Table 1 [person_names] reside)
b) Select "SOURCE" Tab and select the "SQL Query" radio.
c) Enter the following SQL statement
"SELECT some_id,last_name,first_name,mid_name FROM [person_names] A INNER JOIN (SELECT some_ID,max(name_eff_dt) FROM [person_names] GROUP BY some_ID) B ON A.some_ID = B.some_ID AND A.name_eff_dt = B.name_eff_dt"
[NOTE : Please verify the SQL statement above.
We need to get a unique row for each unique SOME_ID with the latest name_eff_dt.Assumint the SQL above does that..lets proceed..]
STEP 5: SELECT "BINDINGS" Tab / Select appropriate Connections /select Table 2 [person_demographics]
STEP 6 : Select "Transformations" Tab. Delete any exising Transformations and Click New. Select "ActiveXScript" and hit OK.
STEP 7: In the Trasformations Option Window Select "Source Columns" and click ">>" to move all the columns to the right.
STEP 8: In the Trasformations Option Window Select "Binding Columns" and move only the following columns :
last_name
first_name
mid_name
some_ID
STEP 9 : Select "GENERAL" Tab and Click on Properties.
STEP 10 : Replace
Main = DTSTransformstat_InsertQuery
BY
Main = DTSTransformStat_UpdateQuery
STEP 11 : OK
STEP 12 : In the "Data Driven Task Properties Window" Select the QUERIES Tab and select "Query Type" as Update
STEP 13: Enter the following SQL :
UPDATE last_name = ?, first_name=?, mid_name=? WHERE some_ID = ?
STEP 14 : Click "Parse / Show parameters"
STEP 15 : Select the appropriate parameter in the appropriate ORDER in the SQL statement above.
STEP 16 : OK
STEP 17 : Right Click on the "Data Driven Query Task" and Execute.
Thanks,
RajKum