Query problem

I have three tables as follows:
Table1:
ClientID StudentID Name
--
1 22 John
2 23 Chen
3 34 Ted
4 22 Bod
Primary Key=ClientID + StudentID
Table2:
InternalID ClientID StudentID Name
---
001 5 22 John
002 2 23 Chen
Primary Key=InternalID
Unique Constraint= ClientID + StudentID
Table3:
InternalID ClientID StudentID Name DateTransfered
---
001 1 22 John 2/2/05
Primary Key=InternalID
Unique Constraint= ClientID + StudentID
Here is what I'd like to do:
I'd like to check every single record in Table1 with Table2 and Table 3
if certain rules arre met,then I'd insert the record into Table2 and if not
set a flag on the record itself in Table1 and move to the next record(for
instance the record wasn't inserted)
How can I do that?
Thanks"ALI-R" <Ray@.Alirezaei.com> wrote in message
news:eTKOlwgHFHA.3936@.TK2MSFTNGP10.phx.gbl...
> I'd like to check every single record in Table1 with Table2 and Table 3
> if certain rules arre met,then I'd insert the record into Table2 and if
not
> set a flag on the record itself in Table1 and move to the next record(for
> instance the record wasn't inserted)
What rules? What flag? Can you post an example of what you actually
want to do?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Sorry I should give you a better information on Table1:
Table1:
ClientID StudentID Name Checked? Inserted? Updated?
---
1 22 John 0 0 0
2 23 Chen 0 0 0
3 34 Ted 0 0 0
4 22 Bod 0 0 0
,ok now here is a one of the rules:
I'd like to check if ClientID of each record exist in Table3 or not,if yes
is the StudentId different with the current record in Table1 or not ,if they
are the same ,I should ignore the record and move to the next record ,if
they are not the same I should insert the current record(in Table1) into
Table 2 and set the following flags in the current record
checked=1,Inserted=1.
So I make the comparisons with Table3 but I insert the record into Table2.
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eB5bo1gHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> "ALI-R" <Ray@.Alirezaei.com> wrote in message
> news:eTKOlwgHFHA.3936@.TK2MSFTNGP10.phx.gbl...
> not
> What rules? What flag? Can you post an example of what you actually
> want to do?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||Check whether this works for you
Insert Into Table2 Select T1.*,1,1 from Table1 T1, Table3 T3 where
T1.ClientId=T3.ClientId and T1.StudentId<>T3.StudentId
Madhivanan|||How about updating the flags in Table1?
Thanks for your help
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109655494.932982.133240@.o13g2000cwo.googlegroups.com...
> Check whether this works for you
> Insert Into Table2 Select T1.*,1,1 from Table1 T1, Table3 T3 where
> T1.ClientId=T3.ClientId and T1.StudentId<>T3.StudentId
> Madhivanan
>|||What do you want to update on Table1?
Madhivanan|||Hi,
How about an "instead of" trigger on the table itself.
/Tobbe
create trigger EnforceRule on table2
instead of insert
as
--Rules for table1
if exists
(
select *
from table1 t1
join inserted i
on i.clientid = t1.clientid
and i.studentid = t1.studentid
)
begin
update t1
set t1.checked = 1
from table1 t1
join inserted i
on i.clientid = t1.clientid
and i.studentid = t1.studentid
end
--Rules for table3
else if exists
(
select *
from table3 t3
join inserted i
on i.internalid = t3.internalid
)
begin
..
end
--If no rules apply
else begin
insert table1
select *
from inserted
end|||oops, correction on the last statement ...
insert table2
select *
from inserted
"Tobbe" wrote:

> Hi,
> How about an "instead of" trigger on the table itself.
> /Tobbe
> create trigger EnforceRule on table2
> instead of insert
> as
> --Rules for table1
> if exists
> (
> select *
> from table1 t1
> join inserted i
> on i.clientid = t1.clientid
> and i.studentid = t1.studentid
> )
> begin
> update t1
> set t1.checked = 1
> from table1 t1
> join inserted i
> on i.clientid = t1.clientid
> and i.studentid = t1.studentid
> end
> --Rules for table3
> else if exists
> (
> select *
> from table3 t3
> join inserted i
> on i.internalid = t3.internalid
> )
> begin
> ...
> end
> --If no rules apply
> else begin
> insert table1
> select *
> from inserted
> end
>|||There are some flags in Table1 that need to be updated ,,for instance if any
record from Table1 is inserted into Table2 ,the equivalent flag needs to be
set,or if it's ignored same thing must happen
thanks
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109662199.214901.129150@.l41g2000cwc.googlegroups.com...
> What do you want to update on Table1?
> Madhivanan
>|||Have you ever used this? How is the performance?
Thanks for the reply
"Tobbe" <Tobbe@.discussions.microsoft.com> wrote in message
news:70F8A13B-A8F9-49CE-BBE2-C475156E48DF@.microsoft.com...
> oops, correction on the last statement ...
> insert table2
> select *
> from inserted
>
> "Tobbe" wrote:
>