Hi

I have something very similar but there are nulls involved, not sure how this will affect the script.

The field want to use derived column is a field with say 12 characters.

Field1 - data is for example

123456abcdef

123456

null

The case statement I want to use is

CASE WHEN FIELD1 IS NULL

THEN NULL

WHEN SUBSTRING(FIELD1 , 7, 6) IS NULL

THEN ' '

ELSE RTRIM(SUBSTRING(FIELD1 , 7, 6))

END as FIELD2

Running this Case statement would give results

'abcdef'

''

null

How would I add then as a derived column.

Then example 2 is tougher

CASE WHEN FIELD1 IS NULL THEN NULL

WHEN SUBSTRING(REPLICATE('0', 6 - LEN(FIELD1)) + CAST(FIELD1 AS VARCHAR(6)), 7, 6) IS NULL

THEN 0

ELSE RTRIM(SUBSTRING(REPLICATE('0', 6 - LEN(FIELD1)) + CAST(FIELD1 AS VARCHAR(6)), 7, 6))

END

My third question is would it be faster running this as a sql statement to import rows, or would a derived column with a fastload on the destination be faster. We are in this case talking about a few hundred million rows.