>and EncryptByKey(Key_GUID('EncryptionKey01'), cast(SSNDecrypt as varchar(255)))
not in (select eid1 from xxxx_datamart.dbo.xx_Member)
You cannot do this. The encryption produces different strings at subsequent application.
You have to get the clear text and compare against the clear text.
Cypher text is not good for comparison.
Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
The Best SQL Server 2005 Training in the World!