Comparing Data of two 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

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing Data of two tables

Author

Topic

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2008-12-31 : 04:09:39

Is there any simple to compare data of two tables?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:11:38

just use like below


SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2
ON t1.PK=t2.PK
WHERE t2.PK IS NULL

to get first tables data which is not in second

and 

SELECT t2.*
FROM table2 t2
LEFT JOIN table1 t1
ON t1.PK=t2.PK
WHERE t1.PK IS NULL
to get ones in t2 not in t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:13:21

you can also use NOT EXISTS also


SELECT *
FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE PK=t1.PK)

SELECT *
FROM table2 t2
WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE PK=t2.PK)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 04:14:16

SELECT t1.*, t2.*
FROM Table1 AS t2
FULL JOIN Table2 AS t2 ON t2.keycolumn = t1.keycolumn
WHERE t1.keycolumn IS NULL OR t2.keycolumn IS NULL

E 12°55'05.63"
N 56°04'39.26"

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2008-12-31 : 04:19:32

i want to compare these tables record by record. and if any difference is found in data i want to insert that different record in another table. any insertion, updation occursm, i want to insert in another table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:23:26

quote:
Originally posted by nishita_s

i want to compare these tables record by record. and if any difference is found in data i want to insert that different record in another table. any insertion, updation occursm, i want to insert in another table.

how many fields does your tables have?

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2008-12-31 : 04:25:35

10 columns r there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:30:55

so you want to compare each columns values in both table and do update/insert?

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2008-12-31 : 04:31:50

Yes i want to compare each column value and if any change is there then want to insert it into another table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 04:33:36

Which values do you want to insert into new table?
Data from table1? From table2?

E 12°55'05.63"
N 56°04'39.26"

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2008-12-31 : 04:35:45

Table1 into table2 if any value doesnt match between two.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:37:17

quote:
Originally posted by nishita_s

Yes i want to compare each column value and if any change is there then want to insert it into another table.


INSERT INTO Table1 (columns...)
SELECT t2.Columns
FROM Table2 t2
WHERE NOT EXISTS(SELECT 1 FROM Table1 WHERE PK=t2.PK)

INSERT INTO Table2 (columns...)
SELECT t1.Columns
FROM Table1 t1
WHERE NOT EXISTS(SELECT 1 FROM Table2 WHERE PK=t1.PK)

UPDATE t2
SET t2.columns=t1.columns...
FROM table2 t2
JOIN table1 t1
ON t1.PK=t2.PK
AND (t1.field1<> t2.field1
OR t1.field2<> t2.field2
...
OR t1.field10<> t2.field10)

UPDATE t1
SET t1.columns=t2.columns...
FROM table2 t2
JOIN table1 t1
ON t1.PK=t2.PK
AND (t1.field1<> t2.field1
OR t1.field2<> t2.field2
...
OR t1.field10<> t2.field10)

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-31 : 04:40:51

quote:
Originally posted by Peso

SELECT t1.*, t2.*
FROM Table1 AS t2
FULL JOIN Table2 AS t2 ON t2.keycolumn = t1.keycolumn
WHERE t1.keycolumn IS NULL OR t2.keycolumn IS NULL

E 12°55'05.63"
N 56°04'39.26"

Hi you had given table1 alias name as t2 and table2 aliasname as t2 which is not allowable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:46:20

quote:
Originally posted by raky

quote:
Originally posted by Peso

SELECT t1.*, t2.*
FROM Table1 AS t2
FULL JOIN Table2 AS t2 ON t2.keycolumn = t1.keycolumn
WHERE t1.keycolumn IS NULL OR t2.keycolumn IS NULL

E 12°55'05.63"
N 56°04'39.26"

Hi you had given table1 alias name as t2 and table2 aliasname as t2 which is not allowable

that was just a typo. he has used t1 as alias at other places except near table name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 05:07:34

SELECT t1.*, t2.*
FROM Table1 AS t1
FULL JOIN Table2 AS t2 ON t2.keycolumn = t1.keycolumn
WHERE t1.keycolumn IS NULL OR t2.keycolumn IS NULL

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources