Difference in tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Difference in tables

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-01-29 : 13:39:10

I have two tables that are designed exactly the same, but one has 109,551 records and the other table has 109,560 records. I need to find the 9 records that are not in the other table.

How would I do that?

The way I am trying to find the 9 that are missing is by using the [Last Name], [First Name] and [Middle Intital].

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-29 : 13:47:06

SELECT * FROM table1 t1
WHERE NOT EXISTS
(SELECT 1 FROM table2 t2 WHERE t1.[Last Name] = t2.[Last Name] AND t1.[First Name] = t2.[First Name]
AND t1.[Middle Intital] = t2.[Middle Intital])

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-29 : 13:51:20

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-01-29 : 14:28:48

When I use this formula it is bring over more than 9 records. I am not sure what I am doing wrong.

SELECT * FROM [AG Administrators-KatesAccess] t1
WHERE NOT EXISTS
(SELECT 1 FROM [AG Administrators] t2 WHERE t1.[Last Name] <> t2.[Last Name] AND t1.[First Name] <> t2.[First Name]
AND t1.[Middle Name] <> t2.[Middle Name])

quote:
Originally posted by tonymorell10

SELECT * FROM table1 t1
WHERE NOT EXISTS
(SELECT 1 FROM table2 t2 WHERE t1.[Last Name] = t2.[Last Name] AND t1.[First Name] = t2.[First Name]
AND t1.[Middle Intital] = t2.[Middle Intital])

mmyers7
Starting Member

1 Post

Posted - 2009-01-29 : 14:29:57

If its SQL2005 - use Tablediff.exe that comes with SQL 2005. Output as script to a file to apply to other table that is missing records(inserts/updates/deletes).

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2009-01-29 : 17:23:34

It may not be the answer you want, but both Redgate and Quest Toad have products that do this and both have free evaluations.

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-29 : 19:34:18

Change the <> to = in the sub-select:


SELECT * FROM [AG Administrators-KatesAccess] t1
WHERE NOT EXISTS
(SELECT 1 FROM [AG Administrators] t2 WHERE t1.[Last Name] = t2.[Last Name] AND t1.[First Name] = t2.[First Name] 
AND t1.[Middle Name] = t2.[Middle Name])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 02:08:53

you can use left join also


SELECT t1.* FROM [AG Administrators-KatesAccess] t1
LEFT JOIN [AG Administrators] t2 ON t1.[Last Name] = t2.[Last Name] AND t1.[First Name] = t2.[First Name] 
AND t1.[Middle Name] = t2.[Middle Name])
WHERE t2.[Last Name] IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources