August 22, 2003 at 12:18 pm
Howdy all -
If this is well known, please excuse my ignorance. I'm moving from the Teradata world to the SQL Server world and I'm having difficulty in something that is very easy in Teradata, but apparently not supported in SQL Server.
I have two tables. I want to merge them. There is a possibility that table2 may have rows identical to some rows in table1.
It is not possible to compare against a PK column to eliminate duplicates.
In Teradata I'd do a very simple query:
SELECT * FROM TABLE1
MINUS
SELECT * FROM TABLE2;
However, it does not appear that SQL Server
supports the MINUS (or EXCEPT) verbs.
I did find a reference in SQL Books online
about how to do this with one column -
SELECT COL1 FROM TABLE1
WHERE COL1 NOT IN
(SELECT * FROM TABLE2);
Which works fine if you can compare on just one column to find the differences, but when I try the following:
SELECT COL1,
COL2,
COL3,
COL4
FROM TABLE1
WHERE (COL1,
COL2,
COL3,
COL4)
NOT IN
(SELECT * FROM TABLE2);
SQL Server has a fit that I'm trying to use
multiple columns.
There has to be a way to do this. . .
Anyone have any suggestions?
Tia
randyv
August 22, 2003 at 1:02 pm
To merge 2 tables into a distinct (no dups) resultset, use UNION:
SELECT MyField1, MyField2
FROM MyTable1
UNION
SELECT MyField1, MyField2
FROM MyTable2
To see if a set of columns is NOTin another table, there are numerous ways:
SELECT * FROM MyTable2 t2
WHERE NOT EXISTS
(
SELECT MyField1, MyField2
FROM MyTable1 t1
WHERE t1.MyField1 = t2.MyField1
AND t1.MyField2 = t2.MyField2
)
-- or
SELECT t2.*
FROM MyTable2
LEFT JOIN MyTable1 t1
ON t2.MyField1 = t2.MyField1
AND t2.MyField2 = t2.MyField2
WHERE t2.MyField1 IS NULL
Is TeraData ANSI standard or a proprietary SQL language for analysis services? I've never heard of MINUS or EXCEPT.
Edited by - jpipes on 08/22/2003 1:03:00 PM
August 23, 2003 at 12:38 am
Try
http://www.sqlservercentral.com/scripts/contributions/458.asp
G.R. Preethiviraj Kulasingham
Chief Technology Officer.
Softlogic Information Systems Limited,
14 De Fonseka Place,
Colombo 05.
Sri Lanka.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply