当没有查找表时如何插入硬编码的转换

Question

我必须插入我们的erp记录从我们的客户关系管理的种族血统。密码不在表格里。我如何插入和做

if EthnicOrigin = Hispanic EthnicCategory = H, etc.

AI  =   American Indian or Alaska Native

INSERT INTO Person 
           (CODE, ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, EthnicCategory, CREATE_DATETIME)
     SELECT
           CODE, ID, FIRST_NAME, MIDDLE_NAME, LAST_NAME, EthnicOrigin, CREATE_DATETIME 
           FROM         Changes


CRM                 ERP 
EthnicOrigin        EthnicCategory  

H   =   Hispanic    
AI  =   American Indian 
A   =   Asian   
B   =   Black or African American   
P   =   Native Hawaiian 
W   =   White   

server 2008 r

Answer 1

您可以在case中使用select表达式。

insert into Person(CODE
                  ,ID
                  ,FIRST_NAME
                  ,MIDDLE_NAME
                  ,LAST_NAME
                  ,CREATE_DATETIME
                  ,EthnicCategory
                  )
select CODE
      ,ID
      ,FIRST_NAME
      ,MIDDLE_NAME
      ,LAST_NAME
      ,CREATE_DATETIME
      ,case EthnicOrigin
           when 'H'  then 'Hispanic'    
           when 'AI' then 'American Indian'
           when 'A'  then 'Asian'
           when 'B'  then 'Black or African American'
           when 'P'  then 'Native Hawaiian'
           when 'W'  then 'White'
         else 'No Ethnicity'
         end as EthnicCategory
from Changes;