this is what i am using currently, but all the values are not being populated properly.

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

CREATE TABLE #Temp (ID INT Identity, OrigField VarChar(Max),f1 VarChar(100),

f2 VarChar(100),f3 VarChar(100),f4 VarChar(100),f5 VarChar(100),f6 VarChar(100),

f7 VarChar(100),f8 VarChar(100),f9 VarChar(100),f10 VarChar(100))

INSERT INTO #Temp (OrigField)

SELECT

(ns.f1) AS OrigField

FROM dbo.NEWDUNDD_SEAF0 AS ns

DECLARE @ID INT, @f VarChar(100), @Remainder VarChar(Max)

WHILE EXISTS (SELECT TOP 1 * FROM #Temp WHERE f1 IS NULL)

BEGIN

SET @ID = (SELECT TOP 1 ID FROM #Temp WHERE f1 IS NULL)

SET @Remainder = (SELECT ((OrigField)) + ' ' FROM #Temp WHERE ID = @ID)

WHILE LEN(@Remainder) > 0

BEGIN

SET @f = LEFT(@Remainder, PATINDEX('% %', @Remainder) -1)

IF (SELECT ID FROM #Temp WHERE ID = @ID AND f1 IS NULL) = @ID UPDATE #Temp SET f1 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f2 IS NULL) = @ID UPDATE #Temp SET f2 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f3 IS NULL) = @ID UPDATE #Temp SET f3 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f4 IS NULL) = @ID UPDATE #Temp SET f4 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f5 IS NULL) = @ID UPDATE #Temp SET f5 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f6 IS NULL) = @ID UPDATE #Temp SET f6 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f7 IS NULL) = @ID UPDATE #Temp SET f7 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f8 IS NULL) = @ID UPDATE #Temp SET f8 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f9 IS NULL) = @ID UPDATE #Temp SET f9 = @f WHERE ID = @ID

ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f10 IS NULL) = @ID UPDATE #Temp SET f10 = @f WHERE ID = @ID

SET @Remainder = RIGHT(@Remainder, LEN(@Remainder) - PATINDEX('% %', @Remainder)+1)

END

END

SELECT * FROM #Temp;