SQL: How to notify user if duplicates are found (using WHERE NOT EXISTS clause)?

user_14712545 0
Viewed 73

I am filling a table with data using a stored procedure, and checking for duplicates with the WHERE NOT EXISTS-clause.

Something like:

CREATE PROCEDURE [dbo].[spMyProcedure]
    @param udtMyUserDefinedTableType
AS
BEGIN
    INSERT INTO tblMyTable
        SELECT
            Column1, Column2, Column3, Column4, Column5
    FROM @param
    WHERE NOT EXISTS ( SELECT 1
                       FROM tblMyTable i
                       INNER JOIN @param o
                       ON i.Column1 = o.Column1
                       AND i.Column3 = o.Column3
                       AND i.Column5 = o.Column5 )
END

As is, my application adds the data, and skips the duplicates: good.

But I would like for the the user to be notified if and when a duplicate row is skipped. Preferably, which exact lines. Possible?

where-clause stored-procedures sql-server
1 Answers

Couldn't you just add a SELECT before you do the insert, like:

SELECT 'Not inserted:', Column1, Column2, Column3, Column4, Column5 
  FROM @param AS o
  WHERE EXISTS
  ( 
    SELECT 1
      FROM dbo.tblMyTable AS i
      WHERE i.Column1 = o.Column1
        AND i.Column3 = o.Column3
        AND i.Column5 = o.Column5 
  );

-- also the insert can be slightly simpler:

INSERT INTO dbo.tblMyTable(column list)
SELECT Column1, Column2, Column3, Column4, Column5
  FROM @param AS o
  WHERE NOT EXISTS ( SELECT 1
                       FROM dbo.tblMyTable AS i
                       WHERE i.Column1 = o.Column1
                         AND i.Column3 = o.Column3
                         AND i.Column5 = o.Column5 );

Of course you'll want to have error handling and wrap this in a transaction to prevent race conditions etc.

user_613050
Related