insert or update at the same time

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
Here is what I want to do:
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 give me some
ideas how to do that.
ThanksRayAll wrote:
> 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
>
> Here is what I want to do:
> 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
1- Insert all the rows from table1 into a temp table if they don't exist
in table 3
2- Update table2 by joining on the temp table for all rows with matching
keys but any mismatch on any other column
3- Insert all the rows from the temp table into Table 2 if they don't
exist
-- Pseudo code and untested
Create Table #NotIn3 (studentID IsntID FN LN)
Insert into #NotIn3 (studentID IsntID FN LN)
Select studentID IsntID FN LN
From Table1 Where Not Exists (
Select * from Table2
Where Table1.StudentID = Table2.StudentID
and Table1.IsntID= Table2.IsntID )
Update Table2
Set FN = t.FN,
LN = t.LN
From Table2 inner join #NotIn3 t
On Table2.StudentID = t.StudentID
and and Table2.IsntID= t.IsntID
where (Table2.FN != t.FN
or Table2.LN != t.LN)
Insert into Table2 (studentID IsntID FN LN))
Select studentID IsntID FN LN
From #NotIn3 t
Where Not Exists (
Select * from Table2
Table2.StudentID = t.StudentID
and Table2.IsntID= t.IsntID )
David Gugick
Imceda Software
www.imceda.com|||Update T2 Set
FN = T1.FN,
LN = T1.LN
From Table1 T1 Join Table2 T2
On T2.StudentID = T1.StudentID
And T2.IsntID = T1.IsntID
Where (T2.FN <> T1.FN Or T2.LN <> T1.LN)
And Not Exists
(Select * From Table3
Where StudentID = T1.StudentID
And IsntID = T1.IsntID)
-- --
Insert Table2(InternalID, studentID,
IsntID, TransferDate)
Select InternalID, studentID,
IsntID, TransferDate
From Table1 T1
Where Not Exist
(Select * From Table3
Where StudentID = T1.StudentID
And IsntID = T1.IsntID)
And Not Exist
(Select * From Table2
Where StudentID = T1.StudentID
And IsntID = T1.IsntID)
"RayAll" wrote:

> 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
>
> Here is what I want to do:
> I'd like to check every single row in Table1 to see if it's in Table3 or n
ot
> (based on studentID IsntID),if it exist there ,we simply ignore it and mo
ve
> 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 update
d
> ***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 give me some
> ideas how to do that.
> Thanks
>
>|||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
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uiHguDOIFHA.572@.tk2msftngp13.phx.gbl...
> RayAll wrote:
>
> 1- Insert all the rows from table1 into a temp table if they don't exist
> in table 3
> 2- Update table2 by joining on the temp table for all rows with matching
> keys but any mismatch on any other column
> 3- Insert all the rows from the temp table into Table 2 if they don't
> exist
> -- Pseudo code and untested
> Create Table #NotIn3 (studentID IsntID FN LN)
> Insert into #NotIn3 (studentID IsntID FN LN)
> Select studentID IsntID FN LN
> From Table1 Where Not Exists (
> Select * from Table2
> Where Table1.StudentID = Table2.StudentID
> and Table1.IsntID= Table2.IsntID )
> Update Table2
> Set FN = t.FN,
> LN = t.LN
> From Table2 inner join #NotIn3 t
> On Table2.StudentID = t.StudentID
> and and Table2.IsntID= t.IsntID
> where (Table2.FN != t.FN
> or Table2.LN != t.LN)
> Insert into Table2 (studentID IsntID FN LN))
> Select studentID IsntID FN LN
> From #NotIn3 t
> Where Not Exists (
> Select * from Table2
> Table2.StudentID = t.StudentID
> and Table2.IsntID= t.IsntID )
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||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
"CBretana" <CBretana@.discussions.microsoft.com> wrote in message
news:E77908C9-02C7-4119-891D-9B10F9AD114B@.microsoft.com...
> Update T2 Set
> FN = T1.FN,
> LN = T1.LN
> From Table1 T1 Join Table2 T2
> On T2.StudentID = T1.StudentID
> And T2.IsntID = T1.IsntID
> Where (T2.FN <> T1.FN Or T2.LN <> T1.LN)
> And Not Exists
> (Select * From Table3
> Where StudentID = T1.StudentID
> And IsntID = T1.IsntID)
> -- --
> Insert Table2(InternalID, studentID,
> IsntID, TransferDate)
> Select InternalID, studentID,
> IsntID, TransferDate
> From Table1 T1
> Where Not Exist
> (Select * From Table3
> Where StudentID = T1.StudentID
> And IsntID = T1.IsntID)
> And Not Exist
> (Select * From Table2
> Where StudentID = T1.StudentID
> And IsntID = T1.IsntID)
>
> "RayAll" wrote:
>|||Run this first, before you run the other two...
Update T1 Set
Status = Case
When Exists
(Select * From Table3
Where StudentID = T1.StudentID
And IsntID = T1.IsntID) Then 1
When Exists
(Select * From Table2
Where StudentID = T1.StudentID
And IsntID = T1.IsntID) Then 2
Else 3 End
From Table1 T1
-- --
"RayAll" 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 in
to
> Table2 ,I'd like it to be 2 and if It has been updated I'd like it to be 3
>
> "CBretana" <CBretana@.discussions.microsoft.com> wrote in message
> news:E77908C9-02C7-4119-891D-9B10F9AD114B@.microsoft.com...
>
>|||Should all these T-Sql statements be placed into a Transaction in a
storedprocedure?
Thanks
"CBretana" <CBretana@.discussions.microsoft.com> wrote in message
news:DB41BB06-0BDD-4875-8126-ABA147543517@.microsoft.com...
> Run this first, before you run the other two...
> Update T1 Set
> Status = Case
> When Exists
> (Select * From Table3
> Where StudentID = T1.StudentID
> And IsntID = T1.IsntID) Then 1
> When Exists
> (Select * From Table2
> Where StudentID = T1.StudentID
> And IsntID = T1.IsntID) Then 2
> Else 3 End
> From Table1 T1
> -- --
>
> "RayAll" wrote:
>|||Well, I understood from your firt post that this was a one-time "Fix the
database" problem... If that's true, putting them in a Stored Proc would
probably be overkill. Just run them in Query ANalyzer.. But YES - DEFINITELY
,
put them inside a transaction, so you can manually check to make sure they
did what you want before you "Commit" the changes for good...
If this is a process you anticipate running often, then a Stored Proc might
be the way to go. and Yes they should still be in a Transaction, but if thi
s
is a process you will run often, then architecturally, you would be better
off writing different Logic, that Processes only Updated and Inserted
Records, (not the entire Table every Time) and put that code into an
Insert/Update Trigger instead. Still, of course, inside a transaction.
"RayAll" wrote:

> Should all these T-Sql statements be placed into a Transaction in a
> storedprocedure?
> Thanks
> "CBretana" <CBretana@.discussions.microsoft.com> wrote in message
> news:DB41BB06-0BDD-4875-8126-ABA147543517@.microsoft.com...
>
>|||This logic must be called several times during night inside a DTS package
,but even inside a dts package dosen't mean that I can't use
Triggers,probelm is that before calling this logic ,I am using some other
features inside the DTS package to validate each row and updating my flags
which I was hoping to use those flags for this logic later.If I want to use
triggers ,my validation and this logic(business rule) might interfere since
they are both using the table ,right?
So for start it's better insode a stored procedure so I can call it after
I'm done with the validation in the table.
I would appreciate if I could know any other suggessions in regards to this
scenario.
Thanks very much for your help
"CBretana" <CBretana@.discussions.microsoft.com> wrote in message
news:CE37456E-5A36-4155-BA3F-3BE04E99E5B7@.microsoft.com...
> Well, I understood from your firt post that this was a one-time "Fix the
> database" problem... If that's true, putting them in a Stored Proc would
> probably be overkill. Just run them in Query ANalyzer.. But YES -
> DEFINITELY,
> put them inside a transaction, so you can manually check to make sure they
> did what you want before you "Commit" the changes for good...
> If this is a process you anticipate running often, then a Stored Proc
> might
> be the way to go. and Yes they should still be in a Transaction, but if
> this
> is a process you will run often, then architecturally, you would be better
> off writing different Logic, that Processes only Updated and Inserted
> Records, (not the entire Table every Time) and put that code into an
> Insert/Update Trigger instead. Still, of course, inside a transaction.
>
> "RayAll" wrote:
>|||I think I See... The DTS package is doing a bunch of stuff, and you want to
run this "CleanUp" Code after each Run of the DTS Package?
Well, if that's true, then to answer your question: If you properly
implemented this as a Trigger, as the DTS package is running Individual
Insert or Update Statements, each one of which would be Inserting or Updatin
g
a finite number of records in a single "Statement"... and "triggering" the
trigger to run. The Trigger would NOT confoct with the DTS Actions, it woul
d
just slow them down. To the degree that the Trigger would be running
multiple times (This Depends on how "granular" each individual Statement
executed by the DTS Package is) this would slow down the overall performace
compared t ojust running a single Stored Proc After the whole DTS Package wa
s
finished...
"RayAll" wrote:

> This logic must be called several times during night inside a DTS package
> ,but even inside a dts package dosen't mean that I can't use
> Triggers,probelm is that before calling this logic ,I am using some other
> features inside the DTS package to validate each row and updating my flags
> which I was hoping to use those flags for this logic later.If I want to us
e
> triggers ,my validation and this logic(business rule) might interfere sinc
e
> they are both using the table ,right?
> So for start it's better insode a stored procedure so I can call it after
> I'm done with the validation in the table.
> I would appreciate if I could know any other suggessions in regards to th
is
> scenario.
> Thanks very much for your help
> "CBretana" <CBretana@.discussions.microsoft.com> wrote in message
> news:CE37456E-5A36-4155-BA3F-3BE04E99E5B7@.microsoft.com...
>
>