Comparing two tables

IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.
thanks in advance for your help!One quick way is to use EM to script each table then compare both files.|||I was referring to the data in the tables. Sorry for the confusion.|||If you know perl, you should do it with a script like that:

#!C:/Perl/bin/perl.exe
# SYNTAXE : perl script.pl server db user pwd

use DBI;

my @.param = @.ARGV;
my $server = $param[0];
my $database = $param[1];
my $user = $param[2];
my $password = $param[3];

my $dsn = "Driver={SQL Server};Server=$server;Database=$database;Uid=$use r;Pwd=$password;" ;
my $dbh = DBI->connect("dbi:ADO:$dsn") or die "Impossible connection: $DBI::errstr";

my $sth1 = $dbh->prepare( q{
SELECT * FROM table1
}) or die "Can't prepare statement: $DBI::errstr";

my $sth2 = $dbh->prepare( q{
SELECT * FROM table2
}) or die "Can't prepare statement: $DBI::errstr";

my $rc1 = $sth1->execute
or die "Can't execute statement: $DBI::errstr";
my $rc2 = $sth1->execute
or die "Can't execute statement: $DBI::errstr";

while ( @.row1 = $sth1->fetchrow_array and @.row2 = $sth2->fetchrow_array )
{
# do your tests here
}

$rc = $dbh->disconnect;|||Sorry, I don't know pearl. Is there anything on sqlserver?|||Don't see what, except a sql script that you have to write (like the one in perl) :)|||IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.

thanks in advance for your help!What exactly do you mean by "items"? Do your tables have Primary Keys (if not, this problems gets incredibly ugly)? What exactly do you want displayed when a difference is found?

The best bet might be to compose a sample set of data that contains a pair of 5 row tables with one row "missing" in each of them, and the output that you'd like to get from comparing them.

-PatP|||You mean like

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
CREATE TABLE myTable00(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd'

INSERT INTO myTable00(Col2)
SELECT 'a' UNION ALL
SELECT 'x' UNION ALL
SELECT 'c' UNION ALL
SELECT 'z'
GO

SELECT a.Col1, a.Col2, b.Col2
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
WHERE a.Col2 <> b.Col2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||Try this:
http://www.sqlservercentral.com/scripts/contributions/596.asp|||Ummm ... isn't it much easier than that? How about

SELECT value1, value2, value3, value4, value5
FROM table1
WHERE value1 NOT IN (SELECT value1 FROM table2)