高效的查询将分隔的列拆分为单独的表

创建一个拆分函数：

CREATE FUNCTION dbo.SplitStrings(@List NVARCHAr(MAX))RETURNS TABLEAS   RETURN ( SELECt Item FROM       ( SELECt Item = x.i.value('(./text())[1]', 'nvarchar(max)')         FROM ( SELECt [XML] = ConVERT(XML, '<i>'         + REPLACE(@List, '.', '</i><i>') + '</i>').query('.')) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y       WHERe Item IS NOT NULL   );GO

然后清除所有游标并循环废话，并执行以下操作：

INSERT dbo.mrhierlookup(  heiraui,  aui)SELECT s.Item, m.aui  FROM dbo.mrhier3 AS m  CROSS APPLY dbo.SplitStrings(m.ptr) AS sGROUP BY s.Item, m.aui;