title: Duplicate entries with same insert statement in SQL Server
body: <p>I am having difficult with duplicate entries in SQL Server 2008 R2. Due to the nature of the program, I have been told unique constraints are not an option.</p>
  
  <p>Can someone please assist as I have tried a For Insert Trigger but no luck. </p>
  
  <p>Table structure:</p>
  
  <pre><code>col1 - identity
  col2 - filename
  col3 - date
  col4 - filetype
  col5 - process
  col6 - userid
  col7 - info
  </code></pre>
  
  <p>When I run this <code>insert</code> the first time, it enters both the entries. Which I do not want:</p>
  
  <pre><code>insert into table(filename, filetype,process, userid)
  values
    ('test9','import','view','tester'),
    ('test9','import','view','tester')
  </code></pre>
  
  <p>The entry is now in the table twice the only difference is the identity column.</p>
  
  <pre><code>Identity     Filename   Date          Filetype     Process    Userid    Info
  17           Test9      2014-01-31    Import       View       test      null
  18           Test9      2014-01-31    Import       View       test      null
  </code></pre>
  
  <p>When I run the exact same insert the second time it then does not insert as it then sees it as a duplicate. Why does it not see it as a duplicate the first time?</p>
  
  <p>Here is the trigger that I created:</p>
  
  <pre><code>Create trigger check_duplicates
  on table
  for insert
  as 
    if not exist (select p.filename, p.date
                  from table p, inserted i
                  where p.filename = i.filename and p.date = i.date)
     insert into table
     select filename, date, filetype, process, userid, info
     from inserted
  else 
    raiserror('Duplicate file exist',16,1)
  
  rollback
  end
  </code></pre>
  
  <p>Thank you</p>