Dynamic column records when doing an insert

[christer99]

How do I dynamically change one column value in the source when doing an insert?

The column TYPE0 has 5 different COLUMN names, TYPE1, TYPE2, TYPE3, TYPE4 and TYPE5, and thus I need to loop the insert statement 5 times) and change the source each time.


INSERT INTO dbpDetails
(ACCREC, STID, OPOWNER, TYPEID, ESTPREM, CREATOR, CREATEDATE, ESTCLOSE, LEADSOURCE)
SELECT PROSP.REC, '1' AS Expr1, PROSP.PR, PolicyType_1.PTID, PROSP.PREM0, PROSP.OP_ID, CONVERT(DateTime,
(CASE WHEN ASCII(substring(ENTERED, 7, 1)) BETWEEN 49 AND 57 THEN LEFT(ENTERED, 6) + '19' + RIGHT(ENTERED, 2)
WHEN ASCII(substring(ENTERED, 7, 1)) = 48 THEN LEFT(ENTERED, 6) + '20' + RIGHT(ENTERED, 2) WHEN ISDATE(CONVERT(varchar(10),
CONVERT(nvarchar(6), LEFT(ENTERED, 6)) + CONVERT(nvarchar(4), ASCII(SUBSTRING(ENTERED, 7, 1)) + 135)) + RIGHT(ENTERED, 1))
= '1' THEN (CONVERT(varchar(10), CONVERT(nvarchar(6), LEFT(ENTERED, 6)) + CONVERT(nvarchar(4), ASCII(SUBSTRING(ENTERED, 7, 1)) + 135))
+ RIGHT(ENTERED, 1)) ELSE '' END)) AS DATEVAL, CONVERT(DateTime, (CASE WHEN ((PROSP.EXP0 IS NULL) OR
(PROSP.EXP0 = '')) THEN NULL WHEN ASCII(substring(PROSP.EXP0, 7, 1)) BETWEEN 49 AND 57 THEN LEFT(PROSP.EXP0, 6)
+ '19' + RIGHT(PROSP.EXP0, 2) WHEN ASCII(substring(PROSP.EXP0, 7, 1)) = 48 THEN LEFT(PROSP.EXP0, 6) + '20' + RIGHT(PROSP.EXP0, 2)
WHEN ISDATE(CONVERT(varchar(10), CONVERT(nvarchar(6), LEFT(PROSP.EXP0, 6)) + CONVERT(nvarchar(4), ASCII(SUBSTRING(PROSP.EXP0, 7, 1))
+ 135)) + RIGHT(PROSP.EXP0, 1)) = '1' THEN (CONVERT(varchar(10), CONVERT(nvarchar(6), LEFT(PROSP.EXP0, 6)) + CONVERT(nvarchar(4),
ASCII(SUBSTRING(PROSP.EXP0, 7, 1)) + 135)) + RIGHT(PROSP.EXP0, 1)) ELSE '' END)) AS EXPVAL, LeadSource.LID
FROM PolicyType PolicyType_1 INNER JOIN
PROSP ON PolicyType_1.TYPE = PROSP.TYPE0 LEFT OUTER JOIN
LeadSource ON PROSP.MPLAN = LeadSource.TME LEFT OUTER JOIN
PolicyType RIGHT OUTER JOIN
OpDetails ON PolicyType.PTID = OpDetails.TYPEID ON PROSP.TYPE0 = PolicyType.TYPE AND PROSP.REC = OpDetails.ACCREC