Compare field from two databases 2

[MDA]

Hi all,

I am trying to compare a like field from two seperate databases on the same server. My goal is to extract only transactions from db1 that are not already in db2.

I thought it would be something easy like...

Select distinct FIELD1 from db1.dbo.TABLE, db2.dbo.TABLE WHERE????

Without the WHERE I get the error: Ambiguous column name 'accoCode'.

Any ideas on how to do this?

Thanks in advance for your help.
Regards,

Mike

 

[tlbroadbent]

I don't understand why you would get that error without the WHERE clause.

Do you select * or accoCode or Filed1 in the query? If you select accoCode and it is on both tables the ambiguity is understandable. If a column is on both tables, SQL Server doesn't know which is referenced unless you qualify the name. db1.dbo.tbl.colname or with an alias for the table a.colname

Also without the where clause your query produces a cross join which will return a lot more records than you want.

Try the following:

Select a.accoCode
From db1.dbo.tbl a
Where Not Exists (Select accoCode From db2.dbo.tbl b where b.accoCode=a.accoCode)
Terry

http://members.home.net/tlbroadbent/index.htm

 

[redlam]

hi mike.
try this:

select distinct
tbl1.field1
from db1.dbo.table tbl1 left join
db2.dbo.table tbl2 on
tbl1.field1 = tbl2.field1
where tbl2.field1 is null

 

[MDA]

Thanks for the help.. works like a charm.. I went with Terry's suggestion. I appreciated your time.

Best regards,

Mike