NgUEhE2B

1(IGNORE_DUP_KEY = ON) or make an exclusive check when doing an INSERT?
2CREATE TABLE [dbo].[TEST](
3    [ColA] [varchar](255) NULL,
4    [ColB] [datetime] NULL,
5    [ColC] [datetime] NULL,
6    [ColD] [int] NOT NULL,
7) ON [PRIMARY]
8	
9ALTER TABLE dbo.TEST
10  ADD CONSTRAINT uniqueRows UNIQUE (ColA, ColB, ColC, ColD) WITH (IGNORE_DUP_KEY = ON)
11	
12INSERT INTO X 
13VALUES(Y,Z)    
14WHERE Y  NOT IN (SELECT Y FROM X)
15	
16INSERT INTO dbo.TEST 
17VALUES(ValA,ValB,ValC,ValD) 
18WHERE (Some complicated check)
19	
20BEGIN TRY
21  INSERT dbo.TEST(ValA, ValB, ValC, ValD)
22    SELECT @ValA, @ValB, @ValC, @ValD
23    WHERE NOT EXISTS
24    (SELECT 1 FROM dbo.TEST
25      WHERE ValA = @ValA AND ValB = @ValB AND ValC = @ValC AND ValD = @ValD);
26END TRY
27BEGIN CATCH
28  PRINT 'Move along, nothing to see here...';
29END CATCH
30	
31IF EXISTS (SELECT 1 FROM @SourceOfMultipleRows AS r
32    WHERE EXISTS (SELECT 1 FROM dbo.Test AS t WHERE t.ValA = r.ValA AND ...))
33  OR EXISTS (SELECT 1 FROM @SourceOfMultipleRows
34    GROUP BY ValA, ValB, ValC, ValD HAVING COUNT(*) > 1)
35  BEGIN
36    PRINT 'Not proceeding at all.';
37  END
38  ELSE
39  BEGIN
40    BEGIN TRY
41      INSERT dbo.TEST(ValA, ValB, ValC, ValD)
42        SELECT ValA, ValB, ValC, ValD
43        FROM @SourceOfMultipleRows AS r
44        WHERE NOT EXISTS (SELECT 1 FROM dbo.Test AS t
45          WHERE t.ValA = r.ValA AND ...)
46        GROUP BY ValA, ValB, ValC, ValD;
47    END TRY
48    BEGIN CATCH
49      PRINT 'Move along, nothing to see here...';
50    END CATCH
51  END
52	
53BEGIN TRY
54  INSERT dbo.TEST(ValA, ValB, ValC, ValD)
55    SELECT ValA, ValB, ValC, ValD
56    FROM @SourceOfMultipleRows AS r
57    WHERE NOT EXISTS (SELECT 1 FROM dbo.Test AS t
58      WHERE t.ValA = r.ValA AND ...)
59    GROUP BY ValA, ValB, ValC, ValD;
60END TRY
61BEGIN CATCH
62  PRINT 'Move along...';
63END CATCH