1

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.)

Jean-Michel Garcia
  • 2,359
  • 2
  • 23
  • 44
jnoel10
  • 295
  • 2
  • 4
  • 14

4 Answers4

2

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

By using the union, you will be removing duplicate rows from the set. The union also wants the columns in each select to be the same data type. Provided the columns that have been named the same in your tables have the same data type, this should produce the output you want.

SELECT IDCUST
    ,NAMECUST
    ,CODETERM
    ,AMTCRLIMT
    ,DATELASTIV
    ,AMTBALDUEH
FROM USA.dbo.VIEWUSA

UNION

SELECT IDCUST
    ,NAMECUST
    ,CODETERM
    ,AMTCRLIMT
    ,DATELASTIV
    ,AMTBALDUEH
FROM MAIN.dbo.VIEWMAIN
Vinnie
  • 3,889
  • 1
  • 26
  • 29
1

Assuming the column names are data types are the same and you are looking for union like syntax over join syntax...

All records in VIEWMAIN that do not have an entry in VIEWUSA.

SELECT customer_id, customer_name
FROM MAIN.dbo.VIEWMAIN
minus
SELECT customer_id, customer_name  
FROM USA.dbo.VIEWUSA

All records in VIEWUSA that are not in VIEWMAIN

SELECT customer_id, customer_name
FROM MAIN.dbo.VIEWUSA
minus
SELECT customer_id, customer_name  
FROM USA.dbo.VIEWMAIN

Records that are only in both tables

SELECT customer_id, customer_name
FROM MAIN.dbo.VIEWUSA
intersect
SELECT customer_id, customer_name  
FROM USA.dbo.VIEWMAIN
Brian
  • 13,412
  • 10
  • 56
  • 82
  • The minus is an Oracle term, In SQL Server you would use Except. http://stackoverflow.com/questions/5557991/minus-vs-except-difference-in-oracle-sql-server – Brian Oct 05 '12 at 20:30
  • Thank you Brian, Gordon's answer worked perfectly for what I needed, but I will definitly use your information for other queries I will have to do. – jnoel10 Oct 05 '12 at 20:41
0

If I understand correctly, this is what you want. This will grab all data from both tables where IDCUST is the same.

EDIT: Try this 

SELECT * 
FROM USA.dbo.VIEWUSA a 
WHERE a.IDCUST NOT IN (SELECT DISTINCT IDCUST FROM MAIN.dbo.VIEWMAIN)
PRNDL Development Studios
  • 3,270
  • 2
  • 35
  • 50