Suppress T-SQL warnings generated by IGNORE_DUP_KEY

Question

Is there a way to suppress warnings generated by inserting duplicate keys on indexes with IGNORE_DUP_KEY = ON? The warning it gives is "Duplicate key was ignored."

I am investigating using this option with a table variable for performance reasons, but it generates a lot of warning messages. This can be a problem since I log the output of my script to a text file, and I don't want the log file to fill up with hundreds of lines of this warning over and over.

The warnings can be reproduced with the following script. Note: this is not my actual script, but merely an example for illustration purposes.

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @data TABLE (x int NOT NULL PRIMARY KEY);
DECLARE @i int;

SET @i = 1;
WHILE @i <= 1000
BEGIN
    INSERT INTO @data (x) VALUES (@i);
    SET @i = @i + 1;
END;

DECLARE @test TABLE (x int NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON));
SET @i = 2;
WHILE @i <= 1000
BEGIN
    INSERT INTO @test (x) SELECT x FROM @data WHERE (x % @i) = 0 AND x > @i
    SET @i = (SELECT MIN(x) FROM @data d WHERE x > @i
        AND NOT EXISTS (SELECT 1 FROM @test t WHERE t.x=d.x));
END;

SELECT COUNT(*) FROM @test;

This is similar to How do I suppress T-SQL warnings when running a script SQL Server 2005?, but the solution there does not work in this case. I have also seen suggestions for using TRY...CATCH, but that does not work either: it appears you can't catch warnings.

I am using SQL Server 2005.

Answer 1

What if you removed the "WITH (IGNORE_DUP_KEY = ON)" option from the table declaration and instead wrapped your INSERT statement using a try/catch block. See the example below:

BEGIN TRY 
  INSERT INTO @test (x) SELECT x FROM @data WHERE (x % @i) = 0 AND x > @i 
END TRY
BEGIN CATCH
  IF NOT (ERROR_NUMBER() = 2627 AND ERROR_SEVERITY() = 14)
    PRINT 'Real error occurred.'
END CATCH