Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Posts
    30

    Unanswered: 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!

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    One quick way is to use EM to script each table then compare both files.

  3. #3
    Join Date
    Oct 2004
    Posts
    30
    I was referring to the data in the tables. Sorry for the confusion.

  4. #4
    Join Date
    Mar 2004
    Posts
    114
    If you know perl, you should do it with a script like that:
    Code:
    #!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=$user;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;

  5. #5
    Join Date
    Oct 2004
    Posts
    30
    Sorry, I don't know pearl. Is there anything on sqlserver?

  6. #6
    Join Date
    Mar 2004
    Posts
    114
    Don't see what, except a sql script that you have to write (like the one in perl)

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Shamsu
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean like

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Sep 2003
    Posts
    364

  10. #10
    Join Date
    Feb 2004
    Posts
    42
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •