Not sure abt performance. Replace table variables with temp tables with indexes for large data. Try this SET NOCOUNT ON

DECLARE @T_Data TABLE

(

ID INT IDENTITY(1, 1), Data NVARCHAR(MAX)

)

INSERT INTO @T_Data

SELECT ',2 Column_Name "Col1" "Col2" , 2 New_Column_Value "A" "B", 2 Old_Column_Value "a" "b"'

UNION

SELECT ',2 Field_Name "F1" "F2", 2 New_Field_Name "F1" "F2", 2 Old_Field_Name "f1" "f2" ,1 Column_Name "Col3" ,1 New_Column_Value "C", 1 Old_Column_Value "c"'

DECLARE @T_FirstLevel TABLE

(

Lvl1ID INT IDENTITY(1, 1), ID INT, Lvl1Data NVARCHAR(MAX)

)

INSERT INTO @T_FirstLevel

SELECT ID, LTRIM(T2.Split.value('.', 'VARCHAR(100)')) AS 'Data'

FROM (SELECT ID, CONVERT(XML, '<d>' + REPLACE(Data, ',', '</d><d>') + '</d>') AS XMLData FROM @T_Data) A

CROSS APPLY XMLData.nodes('/d') as T2(Split)

WHERE LTRIM(T2.Split.value('.', 'VARCHAR(100)')) <> ''

DECLARE @T_SecondLevel TABLE

(

Lvl2ID INT IDENTITY(1, 1), Lvl1ID INT, ID INT, Lvl2Data NVARCHAR(MAX),

ColRow INT

)

INSERT INTO @T_SecondLevel

SELECT Lvl1ID, ID, LTRIM(T2.Split.value('.', 'VARCHAR(100)')) AS 'Data'

, ROW_NUMBER() OVER (PARTITION BY Lvl1ID ORDER BY Lvl1ID, ID) AS ColRow

FROM (SELECT Lvl1ID, ID, CONVERT(XML, '<d>' + REPLACE(Lvl1Data, ' ', '</d><d>') + '</d>') AS XMLData FROM @T_FirstLevel) A

CROSS APPLY XMLData.nodes('/d') as T2(Split)

WHERE LTRIM(T2.Split.value('.', 'VARCHAR(100)')) <> ''

DECLARE @T_Final TABLE

(

Lvl2ID INT, Lvl1ID INT, ID INT, Lvl2Data NVARCHAR(MAX), ColRow INT,

Cnt INT, GrpCnt INT, Batch INT

)

INSERT INTO @T_Final

SELECT T.*, (N.number + 1) AS GrpCnt, NULL AS Batch

FROM (SELECT

Lvl2ID, Lvl1ID, ID, Lvl2Data

, CASE WHEN ColRow > 3 THEN 3 ELSE ColRow END AS ColRow

, COUNT(CASE WHEN PATINDEX('%"%', Lvl2Data) > 0 THEN 1 ELSE NULL END) OVER (PARTITION BY lvl1ID) AS Cnt

FROM@T_SecondLevel) T

LEFT JOIN master..spt_values N ON (N.type = 'p' AND N.number <= (T.Cnt - 1) AND T.ColRow < 3)

ORDER BY T.Lvl2ID

UPDATE F1

SET GrpCnt = F2.Row

FROM @T_Final F1

INNER JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY lvl1ID ORDER BY Lvl2ID) AS Row

FROM @T_Final WHERE GrpCnt IS NULL) F2 ON F1.Lvl2ID = F2.Lvl2ID

WHERE F1.GrpCnt IS NULL

UPDATE F1

SET Batch = F2.Lvl2Data

FROM @T_Final F1

INNER JOIN @T_Final F2 ON F1.lvl1ID = F2.lvl1ID

AND F2.ColRow = 1

SELECT F1.Batch

, MAX(CASE WHEN F1.Lvl2Data LIKE 'Column%' THEN 'Column'

WHEN F1.Lvl2Data LIKE 'Field%' THEN 'Field' END) as 'FieldType'

, MAX(CASE WHEN F1.Lvl2Data IN ('Column_Name', 'Field_Name') AND F2.ColRow = 3 THEN F2.Lvl2Data END) as 'FieldName'

, MAX(CASE WHEN F1.Lvl2Data IN ('New_Column_Value', 'New_Field_Name') AND F2.ColRow = 3 THEN F2.Lvl2Data END) AS 'NewValue'

, MAX(CASE WHEN F1.Lvl2Data IN ('Old_Column_Value', 'Old_Field_Name') AND F2.ColRow = 3 THEN F2.Lvl2Data END) AS 'OldValue'

FROM @T_Final F1

INNER JOIN @T_Final F2 ON F1.Lvl1ID = F2.Lvl1ID AND F1.GrpCnt = F2.GrpCnt AND F1.Batch = F2.Batch

GROUP BY F1.ID, F1.GrpCnt, F1.Batch

ORDER BY 1, 2