differences between 2 similar tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

differences between 2 similar tables

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-01-13 : 09:23:39

hi there ,
i have 2 tables "partner_info" and "my_company_info"


--1st table
CREATE TABLE [dbo].[partner_info](
	[general_id] [nchar](10) NULL,
	[office_partner] [varchar](50) NULL,
	[house_partner] [nchar](10) NULL,
	[service_partner] [varchar](50) NULL
) ON [PRIMARY]

--2nd table
CREATE TABLE [dbo].[My_company_info](
	[general_id] [nchar](10) NULL,
	[co_id] [int] NULL,
	[office_co] [varchar](50) NULL,
	[house_co] [nchar](10) NULL,
	[service_co] [varchar](50) NULL
) ON [PRIMARY]

GO




--now ill put some records in my tables my db its called "sura"
INSERT INTO [sura].[dbo].[partner_info]
           ([general_id]
           ,[office_partner]
           ,[house_partner]
           ,[service_partner])

VALUES 
('c1','usa', 'miami', 'buy'),--dos registros
('c1','canada', 'toronto', 'buy'),
('c2','usa', 'miami', 'buy'),
('c4','usa', 'miami', 'buy'),
('c5','canada', 'toronto', 'buy'),
('c6','usa', 'miami', 'buy'), 
('c7','canada', 'toronto', 'buy'), 
('c8','usa', 'miami', 'buy'), 
('c9','canada', 'toronto', 'buy'),
('c10','usa', 'miami', 'buy'), 
('c11','canada', 'toronto', 'buy') 

INSERT INTO [sura].[dbo].[My_company_info]
           ([general_id]
           ,[co_id]
           ,[office_co]
           ,[house_co]
           ,[service_co])
     VALUES
('c1',1,'usa', 'miami', 'buy'),
('c1',2,'canada', 'toronto', 'buy'),
('c3',3,'canada', 'toronto', 'buy'),
('c4',4,'canada', 'miami', 'buy'),
('c5',5,'canada', 'otawa', 'buy'),
('c6',6,'usa', 'miami', 'sell'), 
('c7',7,'usa', 'NY', 'buy'), 
('c8',8,'canada', 'miami', 'sell'), 
('c9',9,'canada', 'otawa', 'sell'), 
('c10',10, 'canada', 'toronto', 'sell'), 
('c11',11,'canada', 'toronto', 'buy')

and now, its when i really need your help
i want to identify all the differences between these 2 tables , and show me the differences
i need a query that gets this result, and show me the differences

'c1' dont appear because the 2 records with this general_id in the 2 tables are exactly equals

'c2' must appears because doesnt exist in my_company_info

'c3' must appears because doesnt exist in partner_info table

'c4' must appears because have different office in the 2 tables
'c5' must appears because have different house in the 2 tables
'c6' must appears because have different service in the 2 tables

'c7' must appears because have different office and house in the 2 tables

'c8' must appears because have different office and service in the 2 tables

'c9' must appears because the records have different house and service in the 2 tables

'c10' must appears because the records have different office house and service in the 2 tables

'c11' doesnt appear because the records in the 2 tables are exactly equals

example
partner_info my_company_info
general_id general_id
c2 null
null c3

partner_info my_company_info
general_id office_partner general_id office_co
c4 usa c4 canada

partner_info my_company_info
general_id house_partner general_id house_co
c5 toronto c5 otawa

partner_info my_company_info
general_id service_partner general_id service_co
c6 buy c6 sell

partner_info my_company_info
general_id office house general_id office_ co house_co
c7 canada toronto c7 usa ny

and etc......

ill really appreciate your help

many thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 09:44:15

[code]
select p.general_id
,p.[office_partner]
,p.[house_partner]
,p.[service_partner]
,m.[office_co]
,m.[house_co]
,m.[service_co]
from partner_info p
full outer join my_company_info m
on m.general_id = p.general_id
where isnull(m.office_co,'') <> isnull(p.office_partner,'')
or isnull(m.office_co,'') <> isnull(p.office_partner,'')
or isnull(m.house_co,'') <> isnull(p.house_partner,'')
or isnull(m.service_co,'') <> isnull(p.service_partner,'')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-01-13 : 09:54:48

awesome, thanks visakh16 for helpping me at all times

but im still having a problem with this query, because the record with general_id = 'c1' appear in the result, and i dont need it, becasuse the 2 record in the 2 tables are exactly equals,,
could u give me an extra hand with that

thanks a lot again
and have a nice day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 10:03:32

that because you've multiple records for c1. in such cases, how do you determine which row needs to be compared to which row? is there some unique id field in partner_info table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-01-13 : 10:42:57

there isnt some unique id in partner_info table, any idea to solve this,, anyone it doesnt matter wich one, ill folloe u

thanks again

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-01-14 : 08:14:49

hi
select p.general_id
,p.[office_partner]
,p.[house_partner]
,p.[service_partner], m.general_id, m.co_id,
m.[office_co]
,m.[house_co]
,m.[service_co]
from partner_info p
full outer join my_company_info m
on m.general_id = p.general_id
where isnull(m.office_co,'') <> isnull(p.office_partner,'')
or isnull(m.office_co,'') <> isnull(p.office_partner,'')
or isnull(m.house_co,'') <> isnull(p.house_partner,'')
or isnull(m.service_co,'') <> isnull(p.service_partner,'')

ill get all the mistakes that my_company made
example

general_i office_partner house_partner service_partner general_id co_id office_co house_co service_co
c10 usa miami buy c10 10 canada toronto sell

now i want to fix all the mistakes
how could i do? to fix all te mistakes ?
the correct info always is the info into the table partner_info so i want to do something like this with all the mistakes

example with record c10

update my_company_info
set office_co =office_partner, house_co=house_partner, service_co= service_partner
where co_id=10

i want to do something like that but with the all mistakes,, so i dont know may be with a loop , im not sure,, could you give me some advice or example

thanks a lot again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 08:25:24

[code]
update c
set m.[office_co] = case when isnull(m.office_co,'') <> isnull(p.office_partner,'') then p.[office_partner] else m.[office_co] end,
m.[house_co]= case when isnull(m.house_co,'') <> isnull(p.house_partner,'') then p.[house_partner] else m.[house_co] end,
m.[service_co] = case when isnull(m.service_co,'') <> isnull(p.service_partner,'') then p.[service_partner] else m.[service_co] end
from partner_info p
inner join my_company_info m
on m.general_id = p.general_id
where isnull(m.office_co,'') <> isnull(p.office_partner,'')
or isnull(m.house_co,'') <> isnull(p.house_partner,'')
or isnull(m.service_co,'') <> isnull(p.service_partner,'')

insert my_company_info
select p.columns..
from partner_info p
where not exists (select 1 from my_company_info
where general_id = p.general_id)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-01-16 : 10:09:33

that works great

thanks again visakh16
thanks for helping me always

i have another problem

i want to update a third table called "invoice"
and this table doenst have a column called "general_id" , only have the columns
[co_id] [int] NULL,
[office_co] [varchar](50) NULL,
[house_co] [nchar](10) NULL,
[service_co [nchar](10) NULL,

, so after executing your awesome query to get all the mistakes

select p.general_id
,p.[office_partner]
,p.[house_partner]
,p.[service_partner], m.general_id, m.co_id,
m.[office_co]
,m.[house_co]
,m.[service_co]
from partner_info p
full outer join my_company_info m
on m.general_id = p.general_id
where isnull(m.office_co,'') <> isnull(p.office_partner,'')
or isnull(m.office_co,'') <> isnull(p.office_partner,'')
or isnull(m.house_co,'') <> isnull(p.house_partner,'')
or isnull(m.service_co,'') <> isnull(p.service_partner,'')

also i need to update table "invoice"

example with record c10

update invoice
set office_co =office_partner, house_co=house_partner, service_co= service_partner
where co_id=10

i want to do something like that but with the all mistakes,, so i dont know may be with a loop , im not sure,, could you give me some advice or example

thanks a lot again

have a nice day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 10:22:50

the update will just the same as before only difference being tablenames will change and also condition


update i
set i.[office_co] = case when isnull(i.office_co,'') <> isnull(m.office_co,'') then  m.office_co else i.[office_co] end,
i.[house_co]= case when isnull(i.house_co,'') <> isnull(m.house_co,'') then  m.house_co else i.[house_co] end,
i.[service_co] = case when isnull(i.service_co,'') <> isnull(m.service_co,'') then  m.service_co else i.[service_co] end
from invoice i 
inner join my_company_info m
on m.co_id = i.co_id
where isnull(m.office_co,'') <> isnull(i.office_co,'')
or isnull(m.house_co,'') <> isnull(i.house_co,'')
or isnull(m.service_co,'') <> isnull(i.service_co,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources