Compare tables

[simma]

Hello,
I have a table A
I have to create a way where we can compare records to table B and if records match then we should give a flag in table A to corresponding loan as 'Y' or 'N'
Can we do this in sp?
Thanks

 

[kkielak]

Hello,

I assume that table A has some id, so lets take example table A:

create table A (
id int not null,
atr1 int,
atr2 int,

loan char
)
go

now let's have some table B

create table B (
id int not null,
atr1b int,
atr2b int,

)
go

Now you can write SP for comparing desired attributes ( atr1 with atr1b .. and so on) for specified id

create proc sp_compare_a_b
@myid int
as
begin
declare @status int
declare @p1 int
declare @p2 int

set @status=0

select @p1=atr1 from a where id=@myid
select @p2=atr1b from b where id=@myid
if (@p1<>@p2) set @status=1

select @p1=atr2 from a where id=@myid
select @p2=atr2b from b where id=@myid
if (@p1<>@p2) set @status=1


-- ... and so on for each attribue you wan
-- and at the end lets do the update

if (@status=0)
update a set loan='Y' where id=@myid
else
update a set loan='N' where id=@myid
end


If you execute

exec sp_compare_a_b 3

it will check the attributes for id 3 and update accordingly the loan column

best regards
Chris

 

[simma]

Thank you so very much Chris! I will try it right now..

 

[simma]

so @myid is a parameter we have to give each time we check record..
Thanks

 

[kkielak]

Let's look at sample data in the tables:

3> select * from a
4> go
id atr1 atr2 loan
----------- ----------- ----------- ----
1 1 1
2 3 3

(2 rows affected)
1> select * from b
2> go
id atr1b atr2b
----------- ----------- -----------
1 2 2
2 3 3

(2 rows affected)
1>

As you can see the row i A with id=1 is not equal with the same row in B - so running

1> exec sp_compare_a_b 1
2> go
(1 row affected)
1> select * from a
2> go
id atr1 atr2 loan
----------- ----------- ----------- ----
1 1 1 N
2 3 3

(2 rows affected)
1>

the rows with ID=2 are identical on all atributes (checked in the sp) so :

3> exec sp_compare_a_b 2
4> go
(1 row affected)
1> select * from a
2> go
id atr1 atr2 loan
----------- ----------- ----------- ----
1 1 1 N
2 3 3 Y

(2 rows affected)
1>


Chris

 

[simma]

Thanks Cris...So each time we execute sp we have to provide parameter for id...Is there any way an sp checks all the records in Table A and Table B at same time?
Thanks

 

[maswien]

simma,

You don't need a SP, you probably can do it using a SQL like following:


update A set A.loan = C.loan
from
( select distinct A.id as id,
A.atr1 as atr1, A.atr2 as atr2,
case when B.id is null then 'No'
else 'Yes' end as loan
from A left outer join B
on A.id = B.id and A.atr1 = B.atr1b
and A.atr2=B.atr2b) C
where A.id = C.id and A.atr1 = C.atr1
and A.atr2=C.atr2

 

[maswien]

simma,

You don't need a SP, you probably can do it using a SQL like following:


update A set A.loan = C.loan
from
( select distinct A.id as id,
A.atr1 as atr1, A.atr2 as atr2,
case when B.id is null then 'No'
else 'Yes' end as loan
from A left outer join B
on A.id = B.id and A.atr1 = B.atr1b
and A.atr2=B.atr2b) C
where A.id = C.id and A.atr1 = C.atr1
and A.atr2=C.atr2

 

[simma]

Thanks mjia !! I will try this...