I have two identical tables on two DBs, DB1.Table1 and DB2.table2. DB1.Table1 has more data than DB2.Table1. I need to find out what data in DB1.Table1 is not in DB2.Table1, but I can't do it by the ROWGUID column because not all ROWGUID values were carried over the last time the DBs were split, so identical rows may not have the same rowguid value. And I can't use the PK column because that's the Identity column which is not unique between these two tables.
What I need to do is compare the combined columns in one table (not including the Identity and rowguid columns, of course) with the same columns in the other table.
Queries are not my strong point. Does anyone know how I can do this? I'm desperate!
Thanks in advance.
JohnJohn,
Assuming that there is another unique index in addition to the PK and
ROWGUID columns, use that to manage the joins. (If there is no other unique
identifier than the two that are not reliable, then the problem becomes much
harder.) Something like:
SELECT * FROM DB1.Table1
WHERE UniqueString + CONVERT(VARCHAR(10),UniqueInt)
NOT IN
(SELECT UniqueString + CONVERT(VARCHAR(10), UniqueInt)
FROM DB2.Table1)
Russell Fields
"John Steen" <anonymous@.discussions.microsoft.com> wrote in message
news:94B4A891-E2B2-4F43-8059-E34BCD82B5C7@.microsoft.com...
> I have two identical tables on two DBs, DB1.Table1 and DB2.table2.
DB1.Table1 has more data than DB2.Table1. I need to find out what data in
DB1.Table1 is not in DB2.Table1, but I can't do it by the ROWGUID column
because not all ROWGUID values were carried over the last time the DBs were
split, so identical rows may not have the same rowguid value. And I can't
use the PK column because that's the Identity column which is not unique
between these two tables.
> What I need to do is compare the combined columns in one table (not
including the Identity and rowguid columns, of course) with the same columns
in the other table.
> Queries are not my strong point. Does anyone know how I can do this? I'm
desperate!
> Thanks in advance.
> John|||Thanks for the quick reply, Russell. Unfortunately, there's not another unique index on this table other than the ones on the Identity and ROWGUID columns. Is there a way to compare the combined values of the rest of the columns? If not, then I'll have to use the ROWGUID column and hope duplicate records aren't created.
Thanks,
John
-- Russell Fields wrote: --
John,
Assuming that there is another unique index in addition to the PK and
ROWGUID columns, use that to manage the joins. (If there is no other unique
identifier than the two that are not reliable, then the problem becomes much
harder.) Something like:
SELECT * FROM DB1.Table1
WHERE UniqueString + CONVERT(VARCHAR(10),UniqueInt)
NOT IN
(SELECT UniqueString + CONVERT(VARCHAR(10), UniqueInt)
FROM DB2.Table1)
Russell Fields
"John Steen" <anonymous@.discussions.microsoft.com> wrote in message
news:94B4A891-E2B2-4F43-8059-E34BCD82B5C7@.microsoft.com...
> I have two identical tables on two DBs, DB1.Table1 and DB2.table2.
DB1.Table1 has more data than DB2.Table1. I need to find out what data in
DB1.Table1 is not in DB2.Table1, but I can't do it by the ROWGUID column
because not all ROWGUID values were carried over the last time the DBs were
split, so identical rows may not have the same rowguid value. And I can't
use the PK column because that's the Identity column which is not unique
between these two tables.
>> What I need to do is compare the combined columns in one table (not
including the Identity and rowguid columns, of course) with the same columns
in the other table.
>> Queries are not my strong point. Does anyone know how I can do this? I'm
desperate!
>> Thanks in advance.
>> John|||John,
probably no other method than the clumsy way of comparing the whole record
in both table:
set concat_null_yields_null off
select t1.PK
from t1
left outer join t2
on t1.c1+t1.c2+t1.c3+... = t2.c1+t2.c2+t2.c3+...
where t2.PK is null
yields the records in t1 but not in t2, and
select t2.PK
from t2
left outer join t1
on t1.c1+t1.c2+t1.c3+... = t2.c1+t2.c2+t2.c3+...
where t1.PK is null
yields the records in t2 but not in t1. There is a checksum function that
may or may not help the processing. If your table is large, you may need to
cycle through the table.
hth
Quentin
"John Steen" <anonymous@.discussions.microsoft.com> wrote in message
news:3937945E-493B-462B-A57E-51C7818B23FD@.microsoft.com...
> Thanks for the quick reply, Russell. Unfortunately, there's not another
unique index on this table other than the ones on the Identity and ROWGUID
columns. Is there a way to compare the combined values of the rest of the
columns? If not, then I'll have to use the ROWGUID column and hope
duplicate records aren't created.
> Thanks,
> John
> -- Russell Fields wrote: --
> John,
> Assuming that there is another unique index in addition to the PK and
> ROWGUID columns, use that to manage the joins. (If there is no other
unique
> identifier than the two that are not reliable, then the problem
becomes much
> harder.) Something like:
> SELECT * FROM DB1.Table1
> WHERE UniqueString + CONVERT(VARCHAR(10),UniqueInt)
> NOT IN
> (SELECT UniqueString + CONVERT(VARCHAR(10), UniqueInt)
> FROM DB2.Table1)
> Russell Fields
> "John Steen" <anonymous@.discussions.microsoft.com> wrote in message
> news:94B4A891-E2B2-4F43-8059-E34BCD82B5C7@.microsoft.com...
> > I have two identical tables on two DBs, DB1.Table1 and DB2.table2.
> DB1.Table1 has more data than DB2.Table1. I need to find out what
data in
> DB1.Table1 is not in DB2.Table1, but I can't do it by the ROWGUID
column
> because not all ROWGUID values were carried over the last time the
DBs were
> split, so identical rows may not have the same rowguid value. And I
can't
> use the PK column because that's the Identity column which is not
unique
> between these two tables.
> >> What I need to do is compare the combined columns in one table
(not
> including the Identity and rowguid columns, of course) with the same
columns
> in the other table.
> >> Queries are not my strong point. Does anyone know how I can do
this? I'm
> desperate!
> >> Thanks in advance.
> >> John
>
>
