title: Inserting data into SQL Table with Primary Key. For dupes - allow insert error or Select first?
body: <p>Given a table such as:</p>
  
  <pre><code>CREATE TABLE dbo.MyTestData (testdata varchar(50) NOT NULL) 
  
  ALTER TABLE dbo.MyTestData WITH NOCHECK ADD CONSTRAINT [PK_MyTestData] PRIMARY KEY  CLUSTERED (testdata) 
  </code></pre>
  
  <p>And given that we want a unique list of 'testdata' when we are done gathering items to be added from a list of external data with known duplicates... When performing an insert stored procedure should the procedure be written to test for existence or should it just allow for error?  What's the most common practice?  I've always performed the test for existence but was debating this last night...  </p>
  
  <pre><code>CREATE PROCEDURE dbo.dmsInsertTestData @ptestdata VarChar(50)
  AS
    SET NOCOUNT ON
  
    IF NOT EXISTS(SELECT testdata FROM dbo.MyTestData WHERE testdata=@ptestdata)
    BEGIN
      INSERT INTO dbo.MyTestData (testdata ) VALUES (@ptestdata)
    END
  
  RETURN 0
  </code></pre>
  
  <p>or just capture/ignore PK violation errors when executing this one?</p>
  
  <pre><code>CREATE PROCEDURE dbo.dmsInsertTestData @ptestdata VarChar(50)
  AS
    SET NOCOUNT ON
    INSERT INTO dbo.MyTestData (testdata ) VALUES (@ptestdata)
  RETURN 0
  </code></pre>