I have three tables like this.

Table1:
studentID IsntID FN LN
====== ===== === ===
Primary Key :studentID + IsntID

Table2:
InternalID studentID IsntID FN LN
====== ====== ===== === ===
Primary Key :InternalID
Unique constraint: studentID + IsntID

Table3:
InternalID studentID IsntID TransferDate
====== ====== ===== =========
Primary Key :InternalID
Unique constraint: studentID + IsntID


In my DTS package ,I'd like to check every single row in Table1 to see if
it's in Table3 or not
(based on studentID IsntID),if it exist there ,we simply ignore it and move
to the next record.
if it dosen't exist there then I need to check to see if that one exist in
Table2 or not.

***if It exist in Table2 ,then there are two possibilities:
1) If it's exactly the same ,we ignore it
2) If any field is different ,then the record in Table2 needs to be updated

***If it dosen't exist in Table2 ,then I need to insert it there



Sorry ,if it's a lengthy question ,I appreciate if somebody gives me some
ideas how to do that in my DTS package

Thanks

OK

What do you want to do with rows that are in Table 3? Nothing?
If they are not in table 3 you check table 2. If the row is in the
EXACTLY then IGNORE else UPDATE. If it is not there then INSERT


This is easy enough to achieve

The way you explain it suggests you are thinking of a cursor and that is
generally a bad thing


--What is in T1 and not T3
SELECT <col list>
FROM TABLE1 T1 LEFT OUTER JOIN TABLE3 T3
ON T1.StudentID = T3.StudentID
AND T1.IsntID = T3.IsntID
WHERE T3.StudentID IS NULL
AND T3.IsntID IS NULL

--For your Updates of T2 I would probably do a Blanket UPDATE across all
rows that are already there anyway as it would more than likely cost you
more to check each attribute
--Use this as an input to your second query which uses the same idea to
do the inserts into T2

Do the updates before the Inserts otherwise you will end up doing more
UPDATEs than necessary.

Make sense?


Allan


"RayAll" <RayAll (AT) microsft (DOT) com> wrote

-Table3 only contains kind of history,I do nothing with it except a simple
comparing.

As far I underestood ,in your suggession,

Step 1) we get a list of records which is in T1 and don't exist in
T3(history table) => output: bunch of records [I underestood this part]
Step 2) update all T2 records with the output of Step1

Question1: How can I update all records in T2 using the output of step1
Question2: There might be some records in Step1 output that don't exist
in T2-I need to insert them into T2,there is n update for these,How can I
acheieve this?

Step3) Insert the new records into T2 [I didn't underestand this section]


Question3:What task should I use for doing this in a DTS package?


Thanks very much for your help.
Roy


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You have two tasks

1 = DataPump task to do the inserts. This is the rows that are NOT in
T3 and also NOT in T2
2 = ExecuteSQL Task to do the UPDATE

Allan

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

Are these two tasks sequential or both could be done at the same time on T1?

Thanks
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Thanks for your nice reply,I think tht's going to work ,but a quick
question:

In Table1 there is also a status field which I'd like to set to show what
has happened to that row :

For instance :
If the row has been ignored I'd like it to be 1,if it has been inserted into
Table2 ,I'd like it to be 2 and if It has been updated I'd like it to be 3


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Then you do your UPDATEs based on the same criteria as you use to decide
what happens to the other rows.

In your INSERTS you insert a flag value
In your UPDATEs you update the flag value.
You then update again those values that were untouched.

"RayAll" <RayAll (AT) microsft (DOT) com> wrote