I am using SQL Server 2008 and Talend
The problem that I am facing is, I have two tables in two different Databases, they have the same fields and I am trying to query them and have certain criteria met.
The user has the correct privileges to view both tables and all.
Both tables I just need a few fields, so I created views to make life easier: Db USA I created a view
(IN THE VIEW)
"SELECT IDCUST, NAMECUST,CODETERM,AMTCRLIMT,DATELASTIV,AMTBALDUEH
FROM TABLE!
WHERE COUNTRYCODE = 'USA' OR COUNTRYCODE ="U.S.A" //this is for USA server
only the other doesn't have the WHERE clause.
Now what I want to do is query this view with my other DB ('MAIN').
The Criteria is if the IDCUST is the same then I just want the row from 'USA' Database.
I Tried:
SELECT *
FROM USA.dbo.VIEWUSA
UNION
SELECT *
FROM MAIN.dbo.VIEWMAIN
WHERE MAIN.dbo.IDCUST <> USA.dbo.VIEWUSA.IDCUST
It has an error though saying USA.dbo.VIEWUSA.IDCUST was not appropriate.
I tried it as a JOIN but my join did not work at all (the max rows was around 6k and it was querying at 100k before i stopped it.)
I think a union all is the best approach, but you need a slight tweak:
select *
from usa.dbo.ViewUSA
union all
select *
from MAIN.dbo.VIEWMAIN vm
where vm.idcust not in (select idcust from usa.dbo.ViewUSA)