Conditional INSERT

~Bean~ · October 28th, 2005, 10:02 AM

I have a stored procedure that inserts a record, but I only want it to execute if the record is not a duplicate......how can I do the check, and the insert all in one sp?

-------------------------
Beware of programmers with screwdrivers...

Jeff Mason · October 28th, 2005, 11:07 AM

What defines a duplicate?

I think something like:

Code:

    INSERT yourtable (<columnlist>)
    VALUES (<parameterlist>)
    WHERE NOT EXISTS (SELECT * FROM yourtable
                        WHERE <col1> = <parameter1>
                          AND <col2> = <parameter2>
                          AND ... );

The list of WHERE predicates is the list that determines what a duplicate is.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

~Bean~ · October 28th, 2005, 11:23 AM

Well, that gave me an error "incorrect syntax near the keyword 'where'" but I got this to work...

INSERT yourtable (<columnlist>)
SELECT 'val1', val2', etc.
WHERE NOT EXISTS (SELECT * FROM yourtable
                        WHERE <col1> = <parameter1>
                          AND <col2> = <parameter2>

Thanks Jeff!

                          AND ... );

-------------------------
Beware of programmers with screwdrivers...