title: INSERT+SELECT with a unique key
body: <p>The following T-SQL statement does not work because <code>[key]</code> has to be unique and the <code>MAX</code> call in the <code>SELECT</code> statement only seems to be called once. In other words it is only incrementing the key value once and and trying to insert that value over and over. Does anyone have a solution?</p>
  
  <pre><code>INSERT INTO [searchOC].[dbo].[searchTable]
  ([key],
   dataVaultType,
   dataVaultKey,
   searchTerm)
  SELECT (SELECT MAX([key]) + 1 FROM [searchOC].[dbo].[searchTable]) AS [key]
    ,'PERSON' as dataVaultType
    ,[student_id] as dataVaultKey
    ,[email] as searchTerm
  FROM [JACOB].[myoc4Data].[dbo].[users]
  WHERE [email] != '' AND [active] = '1'
  AND [student_id] IN (SELECT [userID] FROM [JACOB].[myoc4Data].[dbo].[userRoles] 
  WHERE ([role] = 'STUDENT' OR [role] = 'FACUTLY' OR [role] = 'STAFF'))
  </code></pre>