sql server (alternate): [quoted text, click to view]

Say table1 and table2 both have a primary key of col1, then do:

select table1.col1, table1.col2, table1.col3, ..., table1.colN
from table1
left join table2 on table2.col1 = table1.col1

Hi,

I'm coming back to Sql Server after 4 years away, using other RDBMS,
and there's a few things I'm struggling to remember how to do (if I
could do them in the first place...)

Main amongst those is EXCEPT syntax.

In DB2, if I have two sets of data and I want to exclude the second set
from the first, I can do:

SELECT col1, col2, col3, ... colN
FROM table1
EXCEPT
SELECT col1, col2, col3, ... colN
FROM table2
;

But SQL Server balks at this. I've had a quick look in the T-SQL help
for EXCEPT, but I didn't find that particularly enlightening. Any
pointers as to how I should be doing this?

Thanks

James

Thanks. From looking around a bit more on this group, INTERSECT &
EXCEPT are implemented in SQL Server 2005, but not in 2000. What
you've said is fine where there's a primary key available, but we've
had situations where that isn't the case and EXCEPT has been an easier
way to deal with this (eg you get given a lot of customer address data
that nobody has bothered to form a key on, and you only want to read in
rows you haven't already got. You *can* do it by comparing columns,
but once you have lots of columns to compare the code gets rather
ugly).

Plus I find EXCEPT easier to read, but that's more personal preference
than a good reason...

James

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi James,

Try talking yoour boss into upgrading to SQL Server 2005 :-)

Alternatively, consider this alternative. Definitely not as clean as
just writing EXCEPT, but (esp. with long column lists) shorter than the
outer join approach:

SELECT col1, col2, col3, ... colN
FROM (SELECT col1, col2, col3, ... colN, 'table1' AS tab
FROM table1
UNION ALL
SELECT col1, col2, col3, ... colN, 'table2' AS tab
FROM table2) AS d
GROUP BY col1, col2, col3, ... colN
HAVING MIN(tab) = 'table1'
AND MAX(tab) = 'table1';


--

Thanks Hugo, that's a neat way of doing it. I'm still badgering my
boss about getting 2005 - desperate to get my hands on MERGE INTO as
well...

[quoted text, click to view]

Hi James,

Just FTR, there's no MERGE INTO in SQL Server 2005. Unfortunately. :-(

--