SQL Server Performance Forum – Threads Archive

Tables Comparation

Hi again, I have two simple tables with 2 fields each. I want to compare these tables and just get the entry(ies) that exists in the second table and does not exist in the first table. Is there a way to do that? Thanks,
Jack Bower
software developer
IATAR Studio
http://iatar.port5.com

---

create table #test1
(id int
,fname varchar(20)
,lname varchar(40)
,position char(10)
,Salutation varchar(20)
) insert into #test1(id, fname, lname, position, salutation)
values (1, ‘Dave’, ‘Cooper’, ‘Manager’, ‘Sr’ ) insert into #test1(id, fname, lname, position, salutation)
values (2, ‘Mark’, ‘Lee’, ‘Supervisor’, ‘Jr’ )
create table #test2
(id int
,fname varchar(20)
,lname varchar(40)
,position char(10)
,Salutation varchar(20)
) insert into #test2(id, fname, lname, position, salutation)
values (1, ‘Dave’, ‘Cooper’, ‘Manager’, ‘Sr’ ) insert into #test2(id, fname, lname, position, salutation)
values (2, ‘Mark’, ‘Lee’, ‘Supervisor’, ‘Jr’ ) insert into #test2(id, fname, lname, position, salutation)
values (3, ‘Jane’, ‘Doe’, ‘Jenitor’, ‘Bussboy’ ) insert into #test2(id, fname, lname, position, salutation)
values (4, ‘Joe’, ‘John’, ‘Developer’, ‘Jr’ ) select * from #test1
select * from #test2
select t.id, t.fname, t.lname, t.position, t.salutation
from #test1 o
right join #test2 t
ono.ID = t.ID
where o.ID IS NULL
id fname lname position salutation
———– ——————– —————————————- ———- ——————–
3 Jane Doe Jenitor Bussboy
4 Joe John Developer Jr (2 row(s) affected)
Also, look BOL for OUTER JOIN(LEFT/RIGHT/FULL). Thanks,
DilliGrg

---

or Select * from table2 t2 where not exists
(select * from table1 where keycol=t2.keycol) Madhivanan Failing to plan is Planning to fail

---

Do you know if there are some tools to help do that, automaticaly, for a large number of tables and database? Thanks, Jack Bower
software developer
IATAR Studio
http://iatar.port5.com

---

Yes, Red Gate SQL compare does this. http://www.red-gate.com/products/SQL_Data_Compare/index.htm

---

Also check these out: http://www.sql-server-performance.com/sql_compare_review.asp
http://www.sql-server-performance.com/sql_data_compare_review.asp Thanks,
DilliGrg

---

Hi, You don’t have to search for a third party tool. SQL 2005 has "tablediff" for data compare. You can make a customised script as u want Madhu

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by madhuottapalam</i><br /><br />Hi,<br /><br />You don’t have to search for a third party tool. SQL 2005 has "tablediff" for data compare. You can make a customised script as u want<br /><br />Madhu<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thats really cool [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

Sorry for this delay, about this problm. To make a script? Ok. sound cool, but how many objects I can compare with that script?
I mean I want a full report about all object from a database (tables, views, triggers, roles, rules…)
And considering that I need to manipulate large databases. Thanks, Jack Bower
software developer
IATAR Studio
http://iatar.port5.com

---

<br /><br />Hi Jack,<br /><br /><br /><br />Thread is started for a simple data comparison, see your first post <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />Madhu

---

]]>