Compare Queries to Compare Two SQL Server Tables

Question

I would like to know how to compare the two records in the table table . I mean, I will compare two database tables that can have different column names, but the same data. But one of them may have more records than the other, so I want to see what is the difference between the two tables. To do this, how to write a sql query? FYI: These two databases are under the same instance of SQL Server.

Table1
------+---------
|name |lastname|
------+---------
|John |rose    |
------+---------
|Demy |Sanches |
------+---------

Table2
------+----------
|name2|lastname2|
------+----------
|John |rose     |
------+----------
|Demy |Sanches  |
------+----------
|Ruby |Core     |
------+----------

Then, after comparing table 1 and table 2, it should return Ruby Core from table 2.

Answer 1

T1 T2, , , T2, T2-T1 T2. , ... - :

SELECT 'Table1' AS TableName, name, lastname FROM
    Table1 OUTER JOIN Table2 ON Table1.name = Table2.name2 
                             AND Table1.lastname = Table2.lastname
WHERE Table2.name2 IS NULL
UNION
SELECT 'Table2' AS TableName, name2 as name, lastname2 as lastname FROM
    Table2 OUTER JOIN Table1 ON Table2.name2 = Table1.name 
                             AND Table2.lastname2 = Table1.lastname
WHERE Table1.name IS NULL

- :)

Answer 2

,

, SQL, ApexSQL Data Diff.

, , , , : http://solutioncenter.apexsql.com/how-to-compare-sql-server-database-tables-with-different-names/

, ApexSQL Data Diff, , , .

- ​​, , , SQL .

Answer 3

Select * from Table1
Except
Select * from Table2

table1 table2

Answer 4

Sql, . , , .

    SELECT 'Table1' AS TableName, name, lastname 
    FROM Table1 
FULL JOIN Table2 ON Table1.name = Table2.name2 
                                 AND Table1.lastname = Table2.lastname

Answer 5

CHECKSUM, , .

:

if not OBJECT_ID('Table1', 'Table') is null drop table Table1
if not OBJECT_ID('Table2', 'Table') is null drop table Table2
create table table1
( id int identity(0, 1), 
   name varchar(128), 
   lastname varchar(128)
)
create table table2
( id int identity(0, 1), 
   name varchar(128), 
   lastname varchar(128)
)
insert into table1 (name, lastname) values ('John', 'rose')
insert into table1 (name, lastname) values ('Demy', 'Sanches')
insert into table2 (name, lastname) values ('John', 'rose')
insert into table2 (name, lastname) values ('Demy', 'Sanches')
insert into table2 (name, lastname) values ('Ruby', 'Core')

select 
    table2.*
from table1
     right outer join table2 on CHECKSUM(table1.name, table1.lastname) = CHECKSUM(table2.name, table2.lastname)
where table1.id is null

. CHDCKSUM MSDN.

Answer 6

dbForge Data Compare SQL Server. . , , . , !

Answer 7

create table #test 
(
    Sno INT IDENTITY(1,1),
    ExpDate VARCHAR(50),
    Amt INT,
    Amt1 INT,
    Amt2 INT,
    SumoAmt INT
)

create table #test1 
(
    Sno INT IDENTITY(1,1),
    ExpDate VARCHAR(50),
    Amt INT,
    Amt1 INT,
    Amt2 INT,
    SumoAmt INT
)

INSERT INTO #test(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,10,40)
INSERT INTO #test(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,20,50)
INSERT INTO #test(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,30,60)
INSERT INTO #test(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',NULL,20,40,70)

INSERT INTO #test1(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,10,40)
INSERT INTO #test1(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,20,50)
INSERT INTO #test1(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',10,20,30,60)
INSERT INTO #test1(Expdate,Amt,Amt1,Amt2,SumoAmt) values ('30-07-2012',NULL,20,40,70)

SELECT MIN(TableName) as TableName, Sno,Expdate,Amt,Amt1,Amt2,SumoAmt
FROM
(
  SELECT '#test' as TableName,Sno,Expdate,Amt,Amt1,Amt2,SumoAmt
  FROM #test
  UNION ALL
  SELECT '#test1' as TableName,Sno,Expdate,Amt,Amt1,Amt2,SumoAmt
  FROM #test1
) tmp
GROUP BY Sno,Expdate,Amt,Amt1,Amt2,SumoAmt
HAVING COUNT(*) = 1
ORDER BY sno

Answer 8

.

(SELECT     *, 'in Table1' AS Comments
FROM       Table1
EXCEPT
SELECT     * , 'in Table1' AS Comments
FROM        Table2)
UNION
(SELECT     *, 'in Table2' AS Comments
FROM       Table2
EXCEPT
SELECT     *, 'in Table2' AS Comments
FROM        Table1)

Answer 9

Firefly will do exactly what you are looking for. It allows you to create two SQL statements and then compare the results of sql queries displaying the missing rows and data differences. Each query can even come from another database, such as an oracle / sql server.

http://download.cnet.com/Firefly-Data-Compare-Tool/3000-10254_4-10633690.html?tag=mncol