Setting error levels -- sql server misc

des_crocker NO[at]SPAM harcourt.com
8/28/2003 2:20:09 AM

Dear all,

Is there a way of changing the error level in SQL? I want to insert
records into a table with a primary key, and there will be some
duplicates. Rather than the statement failing with an error when it
encounters a duplicate key, I want to be able to change the error to a
warning and for it to carry on, i.e. throwing away the duplicates but
inserting the unique records.

I know this used to be possible in Informix (which was a while ago) so
I'm guessing you can do something similar in SQL Server. Don't all
shout at me to use SELECT DISTINCT as I can't! I want to be able to
say:

SELECT surname, forename, salutation, postcode
FROM mytable
GROUP BY surname, postcode

What I want is a *single* record for each surname/postcode combination
- so I can mail each household only once, but I obvioulsy still need
to know the salutation and forename. This obviously errors as a SELECT
statement because of the group by.

To solve this I've actually written a cursor to number the records in
each surname/postcode group then delete the duplicates, but a
colleague said she would have set up a second table with a primary key
on surname & postcode and changed the error level to warning (she used
to work on Informix) and inserted from one to the other.

Just wondered if you could use that solution in SQL Server?

Cheers,

Jacco Schalkwijk
8/28/2003 10:56:55 AM

Hi Des,

Your description means that if there is someone else lving in the same
postcode with the same surname as me either them or me won't get your
mailing. Now that is quite unlikely with my surname where I live, but I
think there are quite a few people called Smith around :-). For the rest of
this post I'll assume that with postcode you actually mean "a unique
combination of postcode, housenumber and streetname".

Also I assume that forename is unique within a household, which is a
sensible assumption to make.
You can then get the information you want with plain SQL without having to
resort to any tricks:

SELECT t.surname, t.forename, t.salutation, t.postcode
FROM mytable t
INNER JOIN (SELECT surname, MAX(forename) as forename, postcode
FROM mytable GROUP BY surname, postcode) AS f
ON t.surname = f.surname AND t.forename = f.surname AND t.postcode =
f.postcode

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

[quoted text, click to view]

sql server misc : Setting error levels