Compare tables

can any one suggested me a better way to compare two tables.
I tried following,
create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
insert into t1(col1,col2) values('c11','c12')
insert into t1(col1,col2) values('c21','c22')
insert into t1(col1,col2) values('c31','c32')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t2(col1,col2) values('c11','c12')
insert into t2(col1,col2) values('c21','c22')
insert into t2(col1,col2) values('c31','c32')
insert into t2(col1,col2) values('c41','c42')
select col1 from
(
select col1,col2 from
t1
union all
select col1,col2 from
t2
) a
group by col1,col2 having count(*)<>2skg,
"What" is it that you want to find out?
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
> can any one suggested me a better way to compare two tables.
> I tried following,
> create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> insert into t1(col1,col2) values('c11','c12')
> insert into t1(col1,col2) values('c21','c22')
> insert into t1(col1,col2) values('c31','c32')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
>
> insert into t2(col1,col2) values('c11','c12')
> insert into t2(col1,col2) values('c21','c22')
> insert into t2(col1,col2) values('c31','c32')
> insert into t2(col1,col2) values('c41','c42')
>
> select col1 from
> (
> select col1,col2 from
> t1
> union all
> select col1,col2 from
> t2
> ) a
> group by col1,col2 having count(*)<>2
>|||Since you do not have any keys and the columns are NULL-able, this is
probably as good as anything else.|||Thanks!!. I want to to know if both the tables have same rows of data. i.e
both tables are same.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
> skg,
> "What" is it that you want to find out?
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||skg,
You can start with something like this:
SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
and work it with IS NOT NULL etc.. depending on your needs and requirements.
A more flexible and robust solution would be to use a third-party software
package to make the changes if required. See (as an example):
http://www.red-gate.com/products/SQ...mpare/index.htm
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
> Thanks!!. I want to to know if both the tables have same rows of data. i.e
> both tables are same.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||Thanks!!! Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
> skg,
> You can start with something like this:
> SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
> and work it with IS NOT NULL etc.. depending on your needs and
> requirements.
> A more flexible and robust solution would be to use a third-party software
> package to make the changes if required. See (as an example):
> http://www.red-gate.com/products/SQ...mpare/index.htm
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
>|||skg
SELECT a.col1,a.col2,b.col1,b.col2
From (Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t1 ) a
Inner Join (
Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t2 ) b
On a.col1 = b.col1 and a.col2 = b.col2
Where a.CheckSum = b.CheckSum
"skg" <skg@.yahoo.com> wrote in message
news:umyMn5dzFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks!!! Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
>