IF EXISTS
(
SELECT 1
FROM(SELECT PKCol FROM A.dbo.XYZ
     EXCEPT
     SELECT PKCol FROM B.dbo.XYZ
    )t
    UNION ALL
    (SELECT PKCol FROM B.dbo.XYZ
     EXCEPT
     SELECT PKCol FROM A.dbo.XYZ
    )u
)
PRINT 'Table data is different'
ELSE
PRINT 'Table data same'

quote:

---

Originally posted by vandana

Hi vishak ,

I tried the above query for practising but i got Table data is different even though i copied same table to other database

---

quote:

---

Originally posted by kalyan.cse05

Thanks for the help but it is giving syntax error.
However is there any thing to compare two database?

kalyan Ashis Dey

---

declare @t1 table
(
id int,
val varchar(10)
)

declare @t2 table
(
id int,
val varchar(10)
)


insert @t1
select 1,'xyz' union all
select 3,'rt' union all
select 4,'sdgvef' 

insert @t2
select 2,'xyz' union all
select 3,'sdgvedvfg' union all
select 5,'qwerwq' 

IF EXISTS
(
SELECT 1
FROM(SELECT id FROM @t1
     EXCEPT
     SELECT id FROM @t2
    )t
    UNION ALL
SELECT 1
FROM
    (SELECT id FROM @t2
     EXCEPT
     SELECT id FROM @t1
    )u
)
PRINT 'Table data is different'
ELSE
PRINT 'Table data same'


output
-----------------------------------
Table data is different


declare @t3 table
(
id int,
val varchar(10)
)
declare @t4 table
(
id int,
val varchar(10)
)

insert @t3
select 1,'xyz' union all
select 3,'rt' union all
select 4,'sdgvef' 

insert @t4
select 1,'xyz' union all
select 3,'rt' union all
select 4,'sdgvef' 


IF EXISTS
(
SELECT 1
FROM(SELECT id FROM @t3
     EXCEPT
     SELECT id FROM @t4
    )t
    UNION ALL
	 
SELECT 1
FROM
    (SELECT id FROM @t4
     EXCEPT
     SELECT id FROM @t3
    )u
)
PRINT 'Table data is different'
ELSE
PRINT 'Table data same'


output
------------------------------
Table data same

quote:

---

Originally posted by kalyan.cse05

same here.. though data is same in both table in different database, still showing "Table data is different"

kalyan Ashis Dey

---

quote:

---

Originally posted by kalyan.cse05

Thanks a lot..
Need one more help. Is there any way to compare data between 2 same database which reside in two different database server.

kalyan Ashis Dey

---

quote:

---

Originally posted by kalyan.cse05

Thanks a lot..
Need one more help. Is there any way to compare data between 2 same database which reside in two different database server.

kalyan Ashis Dey

---