Query across multiple db's

I have two databases on one server. I want do a query across the databases. I
was moving the data and out of 370K records, 24 did not come across. How do I
query to find out which records did not make it across.
Hi,
select * from DB1..tablename where id not in(select id from DB2..tablename)
Thanks
Hari
MCDBA
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> I have two databases on one server. I want do a query across the
databases. I
> was moving the data and out of 370K records, 24 did not come across. How
do I
> query to find out which records did not make it across.
|||Did not work. I think because the PK is across 5 fields
"Hari Prasad" wrote:

> Hi,
> select * from DB1..tablename where id not in(select id from DB2..tablename)
>
> Thanks
> Hari
> MCDBA
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> databases. I
> do I
>
>
|||One way to finese this problem is:
SELECT * FROM DB1..tablename
WHERE CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
NOT IN
(SELECT
CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
FROM DB2..tablename)
That assumes that CHAR(10) will hold the conversion of your identifying
columns. Adjust appropriately.
Russell Fields
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:8655C387-155D-4AE6-BFAF-7DB3DFD50593@.microsoft.com...[vbcol=seagreen]
> Did not work. I think because the PK is across 5 fields
> "Hari Prasad" wrote:
DB2..tablename)[vbcol=seagreen]
How[vbcol=seagreen]
|||Or you could use NOT EXISTS, which in my opinion reads a bit better:
SELECT *
FROM DB1..tablename T1
WHERE NOT EXISTS
(SELECT *
FROM DB2..tablename T2
WHERE T2.IDCol1 = T1.IDCol1
AND T2.IDCol2 = T2.IDCol2
AND T2.IDCol3 = T2.IDCol3
AND T2.IDCol4 = T2.IDCol4
AND T2.IDCol5 = T2.IDCol5)
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23hli%23S7hEHA.3536@.TK2MSFTNGP12.phx.gbl...
> One way to finese this problem is:
> SELECT * FROM DB1..tablename
> WHERE CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> NOT IN
> (SELECT
> CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> FROM DB2..tablename)
> That assumes that CHAR(10) will hold the conversion of your identifying
> columns. Adjust appropriately.
> Russell Fields