title: Is there a way to use compound fields for 'Where not exists'
body: <p>I'm trying to insert rows from table paycom to table users and i only want to insert them if the unique UserName does not already exist. The UserName is generated via the first letter of their first name and last name from the paycom table. I've tried various methods but I'm having trouble comparing Username from the user table with a combination of other fields from the paycom table. I've tried various methods but it tells me that the multipart method can't be found or the field doesn't exist.<br />
  Any idea?<br />
  Thanks</p>
  <pre><code>/****** Script for SelectTopNRows command from SSMS  ******/
  INSERT INTO [test].[dbo].[tblUser] (
         [FirstName]
        ,[LastName]
        ,[Email]
        ,[Location]
        ,[Active]
        ,[Position]
        ,[UserName]
        )
  SELECT [Firstname]
        ,[Lastname]
        ,[WorkEmail]
        ,[Department]
        ,1
        ,[JobTitleCode]
        ,LEFT(FirstName,1) + LastName + pin as t1
  FROM Paycom
  WHERE NOT EXISTS (Select UserName,UserId From [test].[dbo].[tblUser] WHERE tblUser.UserName =[test].[dbo].[tblUser].[Paycom].t1)
  and HireDate &gt;= GETDATE();
  </code></pre>